SQLite Forum

Time difference between two records
Login

Time difference between two records

(1) By anonymous on 2020-08-14 07:42:28 [link] [source]

Hello

Lets assume the following database

CREATE table pos (
  pos NVARCHAR(1),
  start DATETIME,
  CONSTRAINT "pk_pos" PRIMARY KEY ("pos");

INSERT INTO pos (pos, start) VALUES ("A", "2020-01-01 12:00:00");
INSERT INTO pos (pos, start) VALUES ("B", "2020-01-01 15:30:00");
INSERT INTO pos (pos, start) VALUES ("C", "2020-01-01 16:00:00");

I would like to create a view with the following information:

POS Start Duration (h)
A 2020-01-01 12:00:00 3.5
B 2020-01-01 15:30:00 1
C 2020-01-01 16:00:00 ??

where ?? is the value of the last column and it should be calculated by: end_of_year - datetime_of_last_dataset_of_the_year

Ist this possible in one view?

(2) By Gunter Hick (gunter_hick) on 2020-08-14 10:15:33 in reply to 1 [link] [source]

Yes.

Join the table to itself to find the time of the next event.

For the last record, replace the NULL result with the start of the next year. See datetime() function documentation.

To compute the time difference, use the strftime() function documented on the same page.

(3) By Ryan Smith (cuz) on 2020-08-14 11:31:44 in reply to 1 [source]

In addition to what Gunther said, you can also use Window functions (with a sufficiently new version of SQLite)

Example:

  -- SQLite version 3.30.1  [ Release: 2019-10-10 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

CREATE table pos (
  pos NVARCHAR(1),
  start DATETIME,
  CONSTRAINT "pk_pos" PRIMARY KEY ("pos")
);

INSERT INTO pos (pos, start) VALUES
 ("A", "2020-01-01 12:00:00")
,("B", "2020-01-01 15:30:00")
,("C", "2020-01-01 16:00:00")
;


SELECT *
  FROM pos
;

  --  pos |start                
  -- -----|---------------------
  --   A  |2020-01-01 12:00:00  
  --   B  |2020-01-01 15:30:00  
  --   C  |2020-01-01 16:00:00  


WITH DSec(pos, start, baseSeconds, baseNextSeconds, endOfYearSeconds) AS (
    SELECT pos, start, strftime('%s',start),
           MAX(strftime('%s',start)) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),
           strftime('%s','now','start of year','+1 year')
      FROM pos
)
SELECT pos AS POS, start AS Start,
       COALESCE(
         (baseNextSeconds - baseSeconds)/3600.0,
	 (endOfYearSeconds - baseSeconds)/3600.0
       ) AS 'Duration (h)'
  FROM DSec
;

  --  POS |Start                |Duration (h)
  -- -----|---------------------|------------
  --   A  |2020-01-01 12:00:00  |         3.5
  --   B  |2020-01-01 15:30:00  |         0.5
  --   C  |2020-01-01 16:00:00  |      8768.0


Note that the CTE (WITH clause) is not needed and can easily be replaced by a sub-query, but I like it for the sake of clarity.

Feel free to ask if anything is unclear.

Good luck!

Ryan

(4) By John Dennis (jdennis) on 2020-08-15 09:32:15 in reply to 3 [link] [source]

This seems to be assuming that the rows are inserted in the start time sequence. True? Do we know that is how the OP's data is stored?
Can this view be written where the sequence of the inserts is not known, so you have a set of values in no specific order and have to rely on an "order by" to list the set in sequence?

(5) By anonymous on 2020-08-15 11:41:13 in reply to 3 [link] [source]

Thank you very much for your answer. Here are realistic data from my database, to check the code:

CREATE table pos (
  pos NVARCHAR(1),
  start DATETIME,
  CONSTRAINT "pk_pos" 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');

(6.3) By Dan Kennedy (dan) on 2020-08-15 13:57:07 edited from 6.2 in reply to 5 [link] [source]

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;

SQL output is:

    ┌───────┬─────────────────────┬─────────────┐
    │  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     │
    └───────┴─────────────────────┴─────────────┘

(7.1) By Dan Kennedy (dan) on 2020-08-15 13:59:15 edited from 7.0 in reply to 4 [link] [source]

Technically the results are undefined. To fix it, the "OVER" clause should be:

    OVER(ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ORDER BY start)

(8) By Keith Medcalf (kmedcalf) on 2020-08-15 18:00:40 in reply to 6.3 [link] [source]

Note that any of these are only accurate if the start times are in UT1. If it is 'localtime' then the results will be incorrect and that conversion from 'localtime' to ut1 via the 'utc' operand in the datetime functions have varying accuracy depending on the Operating System -- that is to say that the conversion will be accurate for 'now' but not necessarily in the past, nor at the end of the year (depending on the whim of pollitians between now and then)