CODEWITHSUNDEEP
vb.netlinqtimeintervalslinq-group
user1434532
user1434532

Reputation: 57

Linq: Group by Time Interval

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?

Upvotes: 3

Views: 1281

Answers (2)

Mark Hurd
Mark Hurd

Reputation: 10931

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()

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460048

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()
    }

Upvotes: 1

Related Questions