LINQ group by date and time

Question

I'm trying to write a linq statement starting from a UnitTest that group exams by day starting from an hour of the day.

The base conditions are the following one

1st exam with recording date 2024-03-21T11:20:00, Id = 3; 
2nd exam with recording date 2024-03-21T11:50:00, Id = 4; 
3rd exam with recording date 2024-03-21T12:20:00, Id = 5; 
4th exam with recording date 2024-03-21T12:50:00, Id = 6; 
5th exam with recording date 2024-03-22T11:20:00, Id = 7; 
6th exam with recording date 2024-03-22T11:50:00, Id = 8; 
7th exam with recording date 2024-03-22T12:20:00, Id = 9; 
8th exam with recording date 2024-03-23T12:50:00, Id = 10; 
9th exam with recording date 2024-03-23T11:20:00, Id = 11; 
10th exam with recording date 2024-03-23T11:50:00, Id = 12; 
11th exam with recording date 2024-03-23T12:20:00, Id = 13; 
12th exam with recording date 2024-03-23T12:50:00, Id = 24; 

The result to achive is the following if group by one day that start at noon

1st visit with Id = 1 and exams with Id = [3, 4]
2nd visit with Id = 5 and exams with Id = [5, 6, 7, 8]
3rd visit with Id = 9 and exams with Id = [9, 10, 11, 12]
4th visit with Id = 13 and exams with Id = [13, 24]

Or this one if group by couple of day that start at noon

1st visit with Id = 1 and exams with Id = [3, 4]
2nd visit with Id = 5 and exams with Id = [5, 6, 7, 8, 9, 10, 11, 12]
3rd visit with Id = 13 and exams with Id = [13, 24]

This is the first unit test that tests a simple condition

[TestMethod]
public void Convert_TwoExamSplitAtNoon_TwoVisits()
{
    PatientWithExams p = new() { PatientId = 1 };

    p.Exams.Add(new LightExam()
    {
        ExamId = 1,
        ExamType = Micromed.Domain.Archive.Types.ExamFileTypeId.EegTrace,
        RecordingDate = new DateTime(2024, 3, 21, 11, 20, 00)
    });

    p.Exams.Add(new LightExam()
    {
        ExamId = 1,
        ExamType = Micromed.Domain.Archive.Types.ExamFileTypeId.EegTrace,
        RecordingDate = new DateTime(2024, 3, 21, 12, 20, 00)
    });

    List<PatientWithExams> patients = new() { p };

    TimeSpan splitTime = new(12, 00, 00);

    Dictionary<int, List<LightVisit>> visits = _converter.ConvertNew(patients, splitTime);

    // Assert
    Assert.AreEqual(true, visits[1].Count() == 2);
}

I was able to write the method that convert exams into visits but actually I don't consider the break by hour

TimeSpan groupInterval = TimeSpan.FromDays(1);

var eegExams = from e in patient.Exams.Where(e => e.ExamType == Domain.Archive.Types.ExamFileTypeId.EegTrace)
               group e by e.RecordingDate.Ticks / groupInterval.Ticks into g
               select new { day = new DateTime(g.Key * groupInterval.Ticks), Values = g.Count(), Exams = g.DefaultIfEmpty() };

Thanks, Ric

Answer 1

What you try to do is some kind of binning or windowing and is often used for creating histograms. You have a start point and a desired length for the bin or window (e.g. 1 day). Then you iterate through all of your elements and generate the bin/window id and group the elements by this id. Here is a code example, that you can adopt to your needs:

public class Program
{
    static void Main(string[] args)
    {
        var now = DateTime.UtcNow;
        var items = Enumerable.Range(1, 10)
            .Select(i => new Item { Id = i, Occurence = now.AddHours(i * 5) })
            .ToList();

        var twelveHourWindows = items
            .GroupBy(item => GetWindow(item.Occurence, now));

        foreach (var group in twelveHourWindows)
        {
            Console.WriteLine($"Group id: {group.Key}");

            foreach (var item in group)
            {
                Console.WriteLine($"   {item.Id} {item.Occurence}");
            }
        }
    }

    private static DateTime GetWindow(DateTime occurence, DateTime start)
    {
        var diff = occurence - start;
        var steps = (int)Math.Floor(diff.TotalHours / 12);

        return start.AddHours(steps * 12);
    }
}

public class Item
{
    public int Id { get; set; }
    public DateTime Occurence { get; set; }
}

The magic stuff happens within the GetWindow() method. This method gets the starting point and the corresponding value from the current item to calculate the desired window id and returns the value of the window, which should be used for this item.

Hope this helps you to solve your problem.

Update with given example values

After your comment, I tried your example values and to me it looks okay. Here is the code and the results:

public class Program
{
    static void Main(string[] args)
    {
        var now = DateTime.Parse("2023-01-01T12:00:00");
        var items = new[]
        {
            new Item { Id = 1, Occurence = DateTime.Parse("2024-03-21T11:20:00") },
            new Item { Id = 2, Occurence = DateTime.Parse("2024-03-21T11:50:00") },
            new Item { Id = 3, Occurence = DateTime.Parse("2024-03-21T12:20:00") },
            new Item { Id = 4, Occurence = DateTime.Parse("2024-03-21T12:50:00") },
            new Item { Id = 5, Occurence = DateTime.Parse("2024-03-22T11:20:00") },
            new Item { Id = 6, Occurence = DateTime.Parse("2024-03-22T11:50:00") },
            new Item { Id = 7, Occurence = DateTime.Parse("2024-03-22T12:20:00") },
            new Item { Id = 8, Occurence = DateTime.Parse("2024-03-23T12:50:00") },
            new Item { Id = 9, Occurence = DateTime.Parse("2024-03-23T11:20:00") },
            new Item { Id = 10, Occurence = DateTime.Parse("2024-03-23T11:50:00") },
            new Item { Id = 11, Occurence = DateTime.Parse("2024-03-23T12:20:00") },
            new Item { Id = 12, Occurence = DateTime.Parse("2024-03-23T12:50:00") },
        };

        var noonHourWindows = items
            .GroupBy(item => GetWindow(item.Occurence, now));

        foreach (var group in noonHourWindows)
        {
            Console.WriteLine($"Group id: {group.Key:s}");

            foreach (var item in group)
            {
                Console.WriteLine($"   {item.Id} {item.Occurence:s}");
            }
        }
    }

    private static DateTime GetWindow(DateTime occurence, DateTime start)
    {
        var diff = occurence - start;
        var steps = (int)Math.Floor(diff.TotalHours / 24);

        return start.AddHours(steps * 24);
    }
}

And here is the output:

Group id: 2024-03-20T12:00:00
   1 2024-03-21T11:20:00
   2 2024-03-21T11:50:00
Group id: 2024-03-21T12:00:00
   3 2024-03-21T12:20:00
   4 2024-03-21T12:50:00
   5 2024-03-22T11:20:00
   6 2024-03-22T11:50:00
Group id: 2024-03-22T12:00:00
   7 2024-03-22T12:20:00
   9 2024-03-23T11:20:00
   10 2024-03-23T11:50:00
Group id: 2024-03-23T12:00:00
   8 2024-03-23T12:50:00
   11 2024-03-23T12:20:00
   12 2024-03-23T12:50:00

As you can see, I just changed the bin size to match 24 hours and set the starting point to match noon (the choosen day doesn't really matter here) and this looks okay.