Подсчёт затраченного времени в Sqlite 3

Question

Для примера приведу вот такую совсем простую таблицу, в которой хранится время, в которое происходило некоторое действие:

CREATE TABLE periods (
    "id" INTEGER PRIMARY KEY,
    "begin" DATETIME,
    "end" DATETIME);
    
INSERT INTO periods VALUES (1, '2022-01-01 12:00:00', '2022-01-01 13:30:00');
INSERT INTO periods VALUES (2, '2022-01-01 22:00:00', '2022-01-02 01:00:00');
INSERT INTO periods VALUES (3, '2022-01-02 17:00:00', '2022-01-04 05:00:00');
INSERT INTO periods VALUES (4, '2022-01-04 07:00:00', '2022-01-04 13:00:00');
INSERT INTO periods VALUES (5, '2022-01-04 19:00:00', '2022-01-04 22:00:00');

Будем считать, что периоды гарантированно не пересекаются и end всегда больше чем begin.

Можно ли пользуясь только языком SQL составить запрос, который выдал бы кол-во затраченного времени по дням? Для данного примера результат должен быть следующий:

date	total duration
2022-01-01	03:30:00
2022-01-02	08:00:00
2022-01-03	24:00:00
2022-01-04	14:00:00

Можно было бы воспользоваться чем-то вроде GROUP BY date(...). Но я не понимаю как его применить в случае, когда период начинается и заканчивается в разные дни.

Жду советов.

Answer 1

WITH RECURSIVE
dates AS (
    SELECT DATE(MIN("begin")) daydate, 
           DATE(MIN("begin"), '+1 DAY') nextdate,
           DATE(MAX("end")) enddate
    FROM periods
  UNION ALL
    SELECT nextdate,
           DATE(nextdate, '+1 DAY'),
           enddate
    FROM dates
    WHERE daydate < enddate
)
SELECT dates.daydate, 
       SUM(  strftime('%s', MIN(dates.nextdate, periods."end")) 
           - strftime('%s', MAX(dates.daydate, periods."begin"))) total_duration
FROM dates
JOIN periods ON dates.daydate < periods."end"
            AND periods."begin" < dates.nextdate
GROUP BY dates.daydate

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=29fe2ec643b276440a906fee41f40e7a

Результат - в секундах. Если надо, форматируй в иное представление.

БЛОГ НА HUSL

Подсчёт затраченного времени в Sqlite 3

Ответы (1 шт):