Linq group by start to end time in a DateTime column

Question

Well, this is a curious messy request, and I have only limited success. I have a list of Foo classes that have a datetime property and other data. I have a class / row for every minute, with some missing and whole days missing like a holiday or weekend. My goal is to group every day for all lines from start to finish.

So, on some days, my start time can be 9:30 in the morning and the end time is 3:00 in the evening. In this situation, I process the following:

DateTime sd = new DateTime(2000, 1, 1, 9, 30, 0); DateTime ed = new DateTime(2000, 1, 1, 15, 0, 0); var z = Foos.Where(a=> a.FooDate.TimeOfDay >= sd.TimeOfDay && a.FooDate.TimeOfDay < ed.TimeOfDay) .GroupBy(a=>a.FooDate.Date); 

It works great. My problem is that I have a start time of 9pm and an end time of 6am. In this case, I want the foos group to go out for the night, and if 9 pm on Friday, and there are no queues until next Monday, I want the group to spend the weekend. I would even be pleased with the proposal for a request that will always go the next day.

I hope this is clear and appreciate any ideas. I tried many other ways to do this using loops and creating another list of individual dates and such, but I'm not happy with that.

Answer 1

In physics, faced with a relative problem, they choose where zero is. So are we.

 // time range expressed as an example in "absolute" terms DateTime sd = new DateTime(2000, 1, 1, 9, 30, 0); DateTime ed = new DateTime(2000, 1, 2, 6, 0, 0); // time range expressed as a zero and range - "relative" terms TimeSpan zero = sd.TimeOfDay; TimeSpan range = ed.Subtract(sd); //the inputs List<DateTime> myList = new List<DateTime>() { new DateTime(2009, 1, 1, 10, 0, 0), //group1 new DateTime(2009, 1, 1, 17, 0, 0), //group1 new DateTime(2009, 1, 2, 9, 0, 0), //this is filtered new DateTime(2009, 1, 2, 10, 0, 0), //group2 new DateTime(2009, 1, 2, 15, 0, 0), //group2 new DateTime(2009, 1, 3, 3, 0, 0), //group2 new DateTime(2009, 1, 3, 7, 0, 0), //this is filtered new DateTime(2009, 1, 3, 10, 0, 0) //group3 }; //at last, the query. var query = myList .Where(d => d.Subtract(zero).TimeOfDay < range) .GroupBy(d => d.Subtract(zero).Date); // output the results foreach (var g in query) { Console.WriteLine("{0}", g.Count()); foreach (var d in g) { Console.WriteLine(" {0}", d); } } 

Results:

 2 1/1/2009 10:00:00 AM 1/1/2009 5:00:00 PM 3 1/2/2009 10:00:00 AM 1/2/2009 3:00:00 PM 1/3/2009 3:00:00 AM 1 1/3/2009 10:00:00 AM 

Answer 2

It will work, but it may not be prettier than what you are already doing

  private List<groupFoo> groupFoos(List<foo> foos) { //Group by Day into groupFoo var z = foos.GroupBy(a => a.FooDate.ToShortDateString()).Select(x => new groupFoo() { Key = x.Key, Start = x.First().FooDate, End = x.Last().FooDate }).ToList(); //Create new list to hold groups var groupedFoos = new List<groupFoo>(); //add all the good groups to the list groupedFoos.AddRange(z.FindAll(zz => zz.Start.CompareTo(zz.End) != 0)); //Remove all of the good groups from the orignal list groupedFoos.ForEach(gf => z.RemoveAt(z.IndexOf(gf))); //Sort whats left z.Sort((a, b) => { return a.Start.CompareTo(b.Start); }); while (z.Count > 1) { //grab the first 2 var y = z.Take(2); //create a new group foo and add it to the good list groupedFoos.Add(y.Aggregate((a, b) => new groupFoo() { Key = a.Key, Start = a.Start, End = b.End })); //remove the bad ones y.ToList().ForEach(yy => z.RemoveAt(z.IndexOf(yy))); } return groupedFoos; } 

and groupFoo looks like this:

 public class groupFoo { public string Key { get; set; } public DateTime Start { get; set; } public DateTime End { get; set; } } 

the sample that I used

  List<foo> foos = new List<foo>(); foos.Add(new foo() { FooDate = new DateTime(2009, 1, 1, 9, 0, 0) }); foos.Add(new foo() { FooDate = new DateTime(2009, 1, 1, 17, 0, 0) }); foos.Add(new foo() { FooDate = new DateTime(2009, 1, 2, 9, 30, 0) }); foos.Add(new foo() { FooDate = new DateTime(2009, 1, 3, 6, 0, 0) }); foos.Add(new foo() { FooDate = new DateTime(2009, 1, 4, 9, 0, 0) }); foos.Add(new foo() { FooDate = new DateTime(2009, 1, 4, 21, 0, 0) });