linq group by a start time to endtime on DateTime Column

Question

Ok this is kinda a messy query and I am only having limited success with it. I have a list of a Foo class that has a datetime property and other data. I have a class/row for allmost every minute, with some missing and some entire days missing like a holiday or weekend. My goal is to group each day for all rows from a start time to an end time.

So on some days my start time may be 9:30am and endtime 3:00pm. This situation I handle by 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);

This works fine. My problem is sometimes I have a starttime of 9pm and a endtime of 6am. In that case i want the group of foos to go overnight, and if the 9pm is on a friday and there are no rows till the next monday i want the the group to span the weekend. I would even be happy with a suggestion of a query that would just allways go to the next day.

I hope thats clear and appreciate any ideas. I tried alot of other ways to do this with loops and creating another list of distinct dates and such but am not happy with it.

Answer 1

This will work but it may not be any prettier than what you're 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 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) });

Answer 2

In physics, when confronted with a relative problem, they get to choose where zero is. So do 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