SQLite Forum

Time difference between two records
Login
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     │
        └───────┴─────────────────────┴─────────────┘