Select monthly items from a collection of DateTime objects

Question

I previously asked this question to fetch datetime objects and group them through dayOfweek and time

So, just for an example: I take the DateTime collection

List<DateTime> collectionOfDateTime = GetDateColletion(); 

and then grouping by day and time of day, doing this

 var byDayOfWeek = collectionOfDateTime.GroupBy(dt => dt.DayOfWeek + "-" + dt.Hour + "-" + dt.Minute); 

So, at this point, I grouped them by week (constant time), working perfectly.

Now I have a new requirement to group by month, not weekly. When I say "month", it is not the same day of the month, but something like "the first day of every month."

I am trying to figure out which “key” to use in a group to group all the elements corresponding to this monthly logic (first Tuesday of the month, second Friday of every month, etc.).

As an example, I can say that I had these dates to start with;

 var date1 = new DateTime(2013, 1, 4).AddHours(8); // This is the first Friday in Jan var date2 = new DateTime(2013, 2, 1).AddHours(8); // This is the first Friday in Feb var date3 = new DateTime(2013, 1, 5).AddHours(3); // This is the first Sat in Jan var date4 = new DateTime(2013, 2, 2).AddHours(3); // This is the first Sat in Feb var date5 = new DateTime(2013, 2, 2).AddHours(6);  // This is the first Sat in Feb - different time 

If these were the dates that went into the original array, I need a group so that there are three groups in the end.

• The first group will have date1 and date2 in it
• The second group will have date3 and date4.
• date5 will be on its own, as it does not match any of the other groups defined at different times.

Can anyone suggest grouping by these criteria anyway?

Answer 1

It seems to me that this is easier than it looks:

 var byDayOfMonth = from d in dates let h = (d.Day / 7) + 1 group d by new { d.DayOfWeek, h } into g select g; 

The local variable h = (d.Day / 7) + 1 establishes that DayOfWeek does exist during this month.

I run it for testing and get 2 groups, exactly the same as in your example. The keys for these groups are:

 { DayOfWeek = Friday, h = 1 } { DayOfWeek = Saturday, h = 1 } 

Which means there are groups for "first Friday of the month" and "first Saturday of the month."

You can easily expand the d.Hour and / or d.Minute grouping group if you want:

 var byDayOfMonth = from d in dates let h = (d.Day / 7) + 1 group d by new { d.DayOfWeek, h, d.Hour, d.Minute } into g select g; 

Results (for keys only):

 { DayOfWeek = Friday, h = 1, Hour = 8, Minute = 0 } { DayOfWeek = Saturday, h = 1, Hour = 3, Minute = 0 } { DayOfWeek = Saturday, h = 1, Hour = 6, Minute = 0 } 

Answer 2

There is probably an easier way to do this, but this is what comes to me:

From your question, I will state that you need to group everything: from "the first Tuesday of February to the first Monday of March", etc., so that you get these "monthly" intervals, which are a variable number of days - depending on the month, which they start. If so, you really need to break it down into ranges using the day of the year to:

 Group by the First Wednesday of the Month 2013 Group 0 (0-1) All DayOfYear between 0 and 1 2013 Group 1 (2-36) The first Wednesday of the month: January is DayOfYear 2. The first Wednesday of the month: February is DayOfYear 37. etc. 

So, the first range is a function f such that f (32) = 1 (DayOfYear is 32), since it falls in the range from 2 to 37. This f is an indexed set of ranges, the item is in the collection, this DayOfYear falls in and returns this position index as the group number.

You can dynamically build this table by getting your minimum and maximum dates from GetDateCollection to determine the overall range. Since the logic related to dates is a rather complicated topic, I would drop out in a library like NodaTime (in particular arithmetic ), start from the min date, moving day by day until I find the first qualifying day (i.e. "first Monday of the month ") and create a range of 0 until this day - 1 as a group of 0 and push on the indexed collection (possibly ArrayList ). Then loop from that date using LocalDate.PlusWeeks(1) until the month changes by building a new range and not clicking that range on the same indexed collection.

Each time you switch to a new year, you will have to add 365 (or 366 if the previous year is a leap year) to your DayOfYear when creating your indexed collection, as DayOfYear is reset every year.

You now have a range set that acts like a table that groups days into the desired units based on their DayOfYear.

Write a function that intersects the table comparing DayOfYear ( + [365|366] * x , where x is the number of years you are comparing from your minimum year) of the given date against the items in the collection until you find a range this day gets inside and returns this index of this element as a group number. (Alternatively, each range can be Func<DateTime,bool> , which returns true if the provided DateTime falls into that range.)

An alternative data structure for collecting ranges will be an array with a length equal to all days from minimum to maximum dates in your date range, as well as the value for each day of their assigned group number (calculated with ranges as described above). This will work faster for grouping, although performance may not be noticeable if you are working with a smaller dataset (just a few hundred dates).

Answer 3

To group with Linq, perhaps this code will help you:

 List<DateTime> collectionOfDateTime = GetDateColletion(); collectionOfDateTime.GroupBy(s => Convert.ToInt16(s.DayOfWeek) & s.Hour & s.Minute);