Best way to store multiple days of the month in a single column-CodePudding

I have a list of numbers displayed in UI, I want to store those selected numbers in a database.

But I don't want to store that number as a comma-separated number.

I want to store that comma-separated list of numbers as a single number as I have done for a day of the week using the flag enums as shown below:

[Flags]
public enum DaysOfWeek
{

    None = 0,
    Sunday = 1 << 0,
    Monday = 1 << 1,
    Tuesday = 1 << 2,
    Wednesday = 1 << 3,
    Thursday = 1 << 4,
    Friday = 1 << 5,
    Saturday = 1 << 6
}

and i would stored that number as:

var item = new Something() { ActionDay = DaysOfWeek.Tuesday | DaysOfWeek.Sunday; };
context.Save();

But in this case for the day of a month, I have only a number that would range from 1-31 or 1-28. I want to store the days of a week or the days of the month in the same column.

What is the best way to store days of the month in a single column?

Edit:

Basically, I have a flag with frequency (Daily, Weekly, Monthly, or Yearly). and according to the frequency selected it will add to another column name DayRunOn (What Day to run on) : (1-7 for weekly, 1-31 for monthly), for weekly I came up with an idea to use as the Flags Enum but stuck on using for monthly.

CodePudding user response：

If, like Klaus recommended, you store the days as bits of an integer indicating "is on on this day" or "is off on this day" then you could start out with an array of days you want it to be ON:

var onDays = new[] { 2,7,12,24,28,31 };

You could turn this into an integer by summing up the results of shifting 1 by those day numbers:

var asInt = onDays.Sum(x => 1 << (x-1));

//asInt is, in binary, this:   0100 1000 1000 0000 0000 1000 0100 0010
//which represent the ON days:  31  28   24             12    7     2

You could turn it back into an array by &ing the integer with the bitshift of 1, to find out if that bit is set:

var asArr = Enumerable.Range(1, 31).Where(x => (asInt & 1 << (x-1)) > 0).ToArray();

Enumerable.Range generates numbers 1 thru 31, the Where takes the number as x and performs a 1<<(x-1), then &s it with asInt and checks if the result is greater than 0 and returns x if it is. The operation thus looks like:

//for e.g. checking if bit 7 is set:
//asInt  is: 0100 1000 1000 0000 0000 1000 0100 0010
//1<<7-1 is:                               0100 0000
//&'d with asInt is:                       0100 0000
//this is greater than 0

//for e.g. checking if bit 8 is set:
//asInt  is: 0100 1000 1000 0000 0000 1000 0100 0010
//1<<8-1 is:                               1000 0000
//&'d with asInt is:                       0000 0000
//this is = 0

There would probably be faster ways of doing this just with pure bitwise ops, etc, but it'd be a micro-optimization

The only thing I would say against this approach, vs something like a CSV: CSV is simple, you can edit it in your DB query tool by hand without too much thinking. It burns a lot more space, but space is cheap. If you saw your DB table like this:

JobName  RunOnDays
Goodbye  1                  //same in each
Hello    2,7,12,24,28,31    //also obvious as a CSV
Foobar   3                  //and that's day 3, WYSIWYG


JobName  RunOnDays
Goodbye  1                  //same in each
Hello    1216350274         //this is a wtf moment
Foobar   4                  //this is day 3, because the 3rd bit is set. How much confusion is that gonna cause?
Baz      5                  //this is days 1 and 3..

CodePudding user response：

You might need to consider saving them in different columns since it will affect data integrity to save different data meaning at same column. OR You might save one of them persistent to database, and calculate the other on application layer. The following suggested code helps you get current day of week from today data:

var currentDateTime = DateTime.Now;
var currentDayOfMonth = currentDateTime.Day;
var startOfMonthDateTime = currentDateTime.AddDays(-currentDayOfMonth   1);
var startOfMonthDayOfMonth = startOfMonthDateTime.Day;
var startOfMonthDayOfWeek = ((int)startOfMonthDateTime.DayOfWeek);
var numberOfWeeksPassedSoFar = currentDayOfMonth / 7;
var remainingOfDays = currentDayOfMonth - (numberOfWeeksPassedSoFar * 7) - 1;
var currentDayOfWeek = startOfMonthDayOfWeek   remainingOfDays;

Unless in ur db you only interested in days of months and weeks, you just store the actual dates and from there you can figure out the days of months and weeks