Group time series by time intervals (e.g. days) with aggregate of duration

Question

I have a table containing a time series with following information. Each record represents the event of "changing the mode".

 Timestamp        | Mode 
------------------+------
 2018-01-01 12:00 |  1   
 2018-01-01 18:00 |  2   
 2018-01-02 01:00 |  1   
 2018-01-02 02:00 |  2   
 2018-01-04 04:00 |  1   

By using the LEAD function, I can create a query with the following result. Now each record contains the information, when and how long the "mode was active".

Please check the 2nd and the 4th record. They "belong" to multiple days.

 StartDT          | EndDT            | Mode | Duration
------------------+------------------+------+----------
 2018-01-01 12:00 | 2018-01-01 18:00 |  1   |   6:00
 2018-01-01 18:00 | 2018-01-02 01:00 |  2   |   7:00
 2018-01-02 01:00 | 2018-01-02 02:00 |  1   |   1:00
 2018-01-02 02:00 | 2018-01-04 04:00 |  2   |  50:00
 2018-01-04 04:00 | (NULL)           |  1   | (NULL)

Now I would like to have a query that groups the data by day and mode and aggregates the duration.

This result table is needed:

 Date       | Mode | Total
------------+------+-------
 2018-01-01 |  1   |  6:00
 2018-01-01 |  2   |  6:00
 2018-01-02 |  1   |  1:00
 2018-01-02 |  2   | 23:00
 2018-01-03 |  2   | 24:00
 2018-01-04 |  2   | 04:00

I didn't known how to handle the records that "belongs" to multiple days. Any ideas?

Answer 1

The following uses recursive CTE to build a list of dates (a calendar or number table works equally well). It then intersect the dates with date times so that missing dates are populated with matching data. The important bit is that for each row, if start datetime belongs to previous day then it is clamped to 00:00. Likewise for end datetime.

DECLARE @t TABLE (timestamp DATETIME, mode INT);
INSERT INTO @t VALUES
('2018-01-01 12:00', 1),
('2018-01-01 18:00', 2),
('2018-01-02 01:00', 1),
('2018-01-02 02:00', 2),
('2018-01-04 04:00', 1);

WITH cte1 AS (
    -- the min and max dates in your data
    SELECT
        CAST(MIN(timestamp) AS DATE) AS mindate,
        CAST(MAX(timestamp) AS DATE) AS maxdate
    FROM @t
), cte2 AS (
    -- build all dates between min and max dates using recursive cte
    SELECT mindate AS day_start, DATEADD(DAY, 1, mindate) AS day_end, maxdate
    FROM cte1
    UNION ALL
    SELECT DATEADD(DAY, 1, day_start), DATEADD(DAY, 2, day_start), maxdate
    FROM cte2
    WHERE day_start < maxdate
), cte3 AS (
    -- pull end datetime from next row into current
    SELECT
        timestamp AS dt_start,
        LEAD(timestamp) OVER (ORDER BY timestamp) AS dt_end,
        mode
    FROM @t
), cte4 AS (
    -- join datetime with date using date overlap query
    -- then clamp start datetime to 00:00 of the date
    -- and clamp end datetime to 00:00 of next date
    SELECT 
        IIF(dt_start < day_start, day_start, dt_start) AS dt_start_fix, 
        IIF(dt_end > day_end, day_end, dt_end) AS dt_end_fix,
        mode
    FROM cte2
    INNER JOIN cte3 ON day_end > dt_start AND dt_end > day_start
)
SELECT dt_start_fix, dt_end_fix, mode, datediff(minute, dt_start_fix, dt_end_fix) / 60.0 AS total
FROM cte4

DB Fiddle

Answer 2

Thanks everybody!

The answer from Cato put me on the right track. Here my final solution:

DECLARE @Start AS datetime;
DECLARE @End AS datetime;
DECLARE @Interval AS int;


SET @Start = '2018-01-01';
SET @End = '2018-01-05';
SET @Interval = 24 * 60 * 60;



WITH 

cteDurations AS 
    (SELECT [Timestamp] AS StartDT,
            LEAD ([Timestamp]) OVER (ORDER BY [Timestamp]) AS EndDT,
            Mode
     FROM tblLog
     WHERE [Timestamp] BETWEEN @Start AND @End
    ),

cteTimeslots AS
    (SELECT @Start AS StartDT,
            DATEADD(SECOND, @Interval, @Start) AS EndDT
     UNION ALL
     SELECT EndDT,
            DATEADD(SECOND, @Interval, EndDT)
     FROM cteTimeSlots WHERE StartDT < @End
    ),

cteDurationsPerTimesplot AS 
    (SELECT CASE WHEN S.StartDT > C.StartDT THEN S.StartDT ELSE C.StartDT END AS StartDT,
            CASE WHEN S.EndDT < C.EndDT THEN S.EndDT ELSE C.EndDT END AS EndDT,
            C.StartDT AS Slot,
            S.Mode
     FROM cteDurations S 
        JOIN cteTimeslots C ON NOT(S.EndDT <= C.StartDT OR S.StartDT >= C.EndDT)
    )


SELECT  Slot,
        Mode,
        SUM(DATEDIFF(SECOND, StartDT, EndDT)) AS Duration

FROM cteDurationsPerTimesplot
GROUP BY Slot, Mode
ORDER BY Slot, Mode;

With the variable @Interval you are able to define the size of the timeslots.

The CTE cteDurations creates a subresult with the durations of all necessary entries by using the TSQL function LEAD (available in MSSQL >= 2012). This will be a lot faster than an OUTER APPLY.

The CTE cteTimeslots generates a list of timeslots with start time and end time.

The CTE cteDurationsPerTimesplot is a subresult with a JOIN between cteDurations and cteTimeslots. This this the magic JOIN statement from Cato!

And finally the SELECT statement will do the grouping and sum calculation per Slot and Mode.

Once again: Thanks a lot to everybody! Especially to Cato! You saved my weekend!

Regards Oliver

Answer 3

create table ChangeMode ( ModeStart datetime2(7), Mode int )

insert into ChangeMode ( ModeStart, Mode ) values
( '2018-11-15T21:00:00.0000000', 1 ),
( '2018-11-16T17:18:19.1231234', 2 ),
( '2018-11-16T18:00:00.5555555', 1 ),
( '2018-11-16T18:00:01.1234567', 2 ),
( '2018-11-16T19:02:22.8888888', 1 ),
( '2018-11-16T20:00:00.9876543', 2 ),
( '2018-11-17T09:00:00.0000000', 1 ),
( '2018-11-17T23:23:23.0230450', 2 ),
( '2018-11-19T17:00:00.0172839', 1 ),
( '2018-11-20T03:07:00.7033077', 2 )

;
with 
-- Determine the earliest and latest dates.
-- Cast to date to remove the time portion.
-- Cast results back to datetime because we're going to add hours later.
MinMaxDates 
as 
(select cast(min(cast(ModeStart as date))as datetime) as MinDate, 
        cast(max(cast(ModeStart as date))as datetime) as MaxDate from ChangeMode),

-- How many days have passed during that period
Dur
as
(select datediff(day,MinDate,MaxDate) as Duration from MinMaxDates),

-- Create a list of numbers.
-- These will be added to MinDate to get a list of dates.
NumList
as
( select 0 as Num
    union all
    select Num+1 from NumList,Dur where Num<Duration ),

-- Create a list of dates by adding those numbers to MinDate
DayList 
as
( select dateadd(day,Num,MinDate)as ModeDate from NumList, MinMaxDates  ),

-- Create a list of day periods
PeriodList
as
( select ModeDate as StartTime,
            dateadd(day,1,ModeDate) as EndTime
            from DayList                        ),

-- Use LEAD to get periods for each record
-- Final record would return NULL for ModeEnd
-- We replace that with end of last day
ModePeriodList
as
( select ModeStart, 
            coalesce( lead(ModeStart)over(order by ModeStart),
                    dateadd(day,1,MaxDate) ) as ModeEnd, 
            Mode from ChangeMode, MinMaxDates               ),

ModeDayList
as
( select * from ModePeriodList, PeriodList 
where ModeStart<=EndTime and ModeEnd>=StartTime
),

-- Keep the later   of the mode start time, and the day start time
-- Keep the earlier of the mode   end time, and the day   end time
ModeDayPeriod
as
( select case when ModeStart>=StartTime then ModeStart  else StartTime end as StartTime,
            case when ModeEnd<=EndTime  then ModeEnd else EndTime   end as EndTime,
            Mode from ModeDayList ),

SumDurations
as
( select cast(StartTime as date) as ModeDate, 
        Mode, 
        DateDiff_Big(nanosecond,StartTime,EndTime)
        /3600000000000 
            as DurationHours from ModeDayPeriod   )                        

-- List the results in order
-- Use MaxRecursion option in case there are more than 100 days 
select ModeDate as [Date], Mode, sum(DurationHours) as [Total Duration Hours]
     from SumDurations 
group by ModeDate, Mode
order by ModeDate, Mode
option (maxrecursion 0)

Result is:

Date       Mode        Total Duration Hours
---------- ----------- ---------------------------------------
2018-11-15 1           3.00000000000000
2018-11-16 1           18.26605271947221
2018-11-16 2           5.73394728052777
2018-11-17 1           14.38972862361111
2018-11-17 2           9.61027137638888
2018-11-18 2           24.00000000000000
2018-11-19 1           6.99999519891666
2018-11-19 2           17.00000480108333
2018-11-20 1           3.11686202991666
2018-11-20 2           20.88313797008333

Answer 4

you could use a CTE to create a table of days then join the time slots to it

DECLARE @MAX as datetime2 = (SELECT MAX(CAST(Timestamp as date)) MX FROM process);
WITH StartEnd AS (select p1.Timestamp StartDT, 
                         P2.Timestamp  EndDT ,
                         p1.mode
                            from process p1
                            outer apply 
                            (SELECT TOP 1 pOP.*  FROM 
                                                    process pOP 
                                                    where pOP.Timestamp > p1.Timestamp 
                                                    order by pOP.Timestamp asc) P2
                 ),
    CAL AS (SELECT (SELECT MIN(cast(StartDT as date)) MN FROM StartEnd) DT
            UNION ALL
            SELECT DATEADD(day,1,DT) DT FROM CAL WHERE CAL.DT < @MAX
            ),
    TMS AS 
    (SELECT CASE WHEN S.StartDT > C.DT THEN S.StartDT ELSE C.DT END AS STP,
           CASE WHEN S.EndDT < DATEADD(day,1,C.DT) THEN S.ENDDT ELSE DATEADD(day,1,C.DT) END AS STE
     FROM StartEnd S JOIN CAL C ON NOT(S.EndDT <= C.DT OR S.StartDT>= DATEADD(day,1,C.dt))
    )
    SELECT *,datediff(MI ,TMS.STP, TMS.ste) as x from TMS