Linq: Group by Time Interval

Question

I've the following Datatable:

[Date];[Time];[ProcessID];[ID]
24.09.2012;08:18:00;4000;1
24.09.2012;08:18:00;4000;2
24.09.2012;08:19:00;4001;3
24.09.2012;08:23:00;4002;4
24.09.2012;08:32:00;4003;5
...

As result I would like to have a grouping by a 15-minute Time-Interval to count the ProcessID´s:

[Interval];[ProcessID Count]
8:15:00 - 8:29:00;3
8:30:00 - 8:45:00;1

How to achieve this goal?

Is `Date` of type `Date` and `Time` of type ...?? Why don't you use one column for both instead? What have you tried? If possible you should do it in the dbms instead. — Tim Schmelter, Sep 24 '12 at 10:15
Yes, the Date Column is a Date type and the Time Column is a Time type. I've to handle this, because it's given by my erp software. — user1434532, Sep 24 '12 at 11:14

Mark Hurd · Accepted Answer · 2012-09-29T08:30:47.627

Here's the solution against Linq-to-SQL (on SQL Server) with the DateTime being in a single field (a LINQpad query):

Const BaseDate As Date = #9/24/2012#
Const PeriodMinutes As Integer = 15
Dim p = From t In TestData _
      Group By q=CInt(Math.Floor((t.SomeDate - BaseDate).TotalMinutes/PeriodMinutes)) Into Group _
      Select s=BaseDate.AddMinutes(q*PeriodMinutes), _
             e=BaseDate.AddMinutes((q+1)*PeriodMinutes), _
             ids=Aggregate g In Group Select g.ProcessID Distinct Into Count()
p.Dump

Using different types and other "little" adjustments could produce simpler SQL backend code.

For your actual database, you need to adjust the Group By, although it now depends upon what your Time column is converted to through Linq-to-SQL and/or your database's driver.

As it is a TimeSpan the Group By becomes something like:

 Group By d=t.[Date], q=CInt(Math.Floor(t.[Time].TotalMinutes/PeriodMinutes)) Into Group _
    Select s=d.AddMinutes(q*PeriodMinutes), _
           e=d.AddMinutes((q+1)*PeriodMinutes), _
           ids=Aggregate g In Group Select g.ProcessID Distinct Into Count()

Tim Schmelter · Answer 2 · 2012-09-24T14:55:31.660

This should work (~~however, not tested~~):

Dim groupedByQuarter =
    From r In table
    Let day = r.Field(Of Date)("Date").Date
    Let time = r.Field(Of TimeSpan)("Time")
    Let quarter = time.Add(TimeSpan.FromMinutes((-1) * time.Minutes Mod 15))
    Group r By Key = New With {Key .day = day, Key .quarter = quarter} Into TimeGroup = Group
    Select New With {
        .Interval = String.Format("{0} - {1}",
                Key.quarter,
                Key.quarter.Add(TimeSpan.FromMinutes(14))),
        .ProcessCount = TimeGroup.Select(Function(r) r.Field(Of Int32)("ProcessID")).Distinct().Count()
    }

Linq: Group by Time Interval

2 Answers2