Easiest way is to use the lead() windows function: CREATE table pos (pos NVARCHAR(1), start DATETIME, PRIMARY KEY(pos, start)); INSERT INTO POS (pos, start) VALUES ('0', '2019-01-01 00:00:00'), ('1', '2019-06-02 11:00:00'), ('2-3', '2019-06-03 11:30:00'), ('4-5', '2019-06-04 00:30:00'), ('6', '2019-06-04 03:00:00'), ('7-9', '2019-06-04 20:20:00'), ('10', '2019-06-05 21:43:00'), ('11-12', '2019-06-26 09:13:00'), ('13', '2019-06-28 06:00:00'), ('14-16', '2019-06-28 11:00:00'), ('17-18', '2019-07-01 17:00:00'), ('19-20', '2019-07-02 02:45:00'), ('0', '2019-07-03 02:15:00'); .mode box WITH pos_with_end(pos, start, end) AS ( SELECT pos, start, lead(start, 1, datetime(start, 'start of year', '+1 year')) OVER (ORDER BY start) FROM pos ) SELECT pos, start, round((strftime('%s', end) - strftime('%s', start)) / 3600.0, 2) AS 'duration(h)' FROM pos_with_end; <p>SQL output is:</p> ┌───────┬─────────────────────┬─────────────┐ │ pos │ start │ duration(h) │ ├───────┼─────────────────────┼─────────────┤ │ 0 │ 2019-01-01 00:00:00 │ 3659.0 │ │ 1 │ 2019-06-02 11:00:00 │ 24.5 │ │ 2-3 │ 2019-06-03 11:30:00 │ 13.0 │ │ 4-5 │ 2019-06-04 00:30:00 │ 2.5 │ │ 6 │ 2019-06-04 03:00:00 │ 17.33 │ │ 7-9 │ 2019-06-04 20:20:00 │ 25.38 │ │ 10 │ 2019-06-05 21:43:00 │ 491.5 │ │ 11-12 │ 2019-06-26 09:13:00 │ 44.78 │ │ 13 │ 2019-06-28 06:00:00 │ 5.0 │ │ 14-16 │ 2019-06-28 11:00:00 │ 78.0 │ │ 17-18 │ 2019-07-01 17:00:00 │ 9.75 │ │ 19-20 │ 2019-07-02 02:45:00 │ 23.5 │ │ 0 │ 2019-07-03 02:15:00 │ 4365.75 │ └───────┴─────────────────────┴─────────────┘