Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > select version(); > > <pre> > | version | > | :------------------------------------------------------------------------------------------------------------------------------- | > | PostgreSQL 12.0 (Debian 12.0-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit | > </pre> <!-- --> > CREATE TABLE truck_loc > ( > truck_id VARCHAR(50), > location_name VARCHAR(12), > loc_date_time TIMESTAMP > ); > > <pre> > ✓ > </pre> <!-- --> > INSERT INTO truck_loc (truck_id, location_name, loc_date_time) > VALUES > ('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'), > ('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'), > ('MonsterTruck', 'Home', '2019-10-22 10:33:10.000'), > ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), > ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), > ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), > ('MonsterTruck', 'Gas Station', '2019-10-22 12:43:00.000'), > ('MonsterTruck', 'Gas Station', '2019-10-22 13:13:00.000'), > ('MonsterTruck', 'Gas Station', '2019-10-22 13:43:00.000'), > ('MonsterTruck', 'Home', '2019-10-22 16:43:00.000'), > ('MonsterTruck', 'Home', '2019-10-22 16:49:00.000'), > ('MonsterTruck', 'Home', '2019-10-22 17:43:00.000'), > ('MonsterTruck', 'Home', '2019-10-22 20:43:00.000'), > ('MonsterTruck', 'Home', '2019-10-22 20:56:00.000'); > > <pre> 14 rows affected > </pre> <!-- --> > -- SELECT * FROM truck_loc; > > <pre> > ✓ > </pre> <!-- --> > SELECT > truck_id, > location_name, > loc_date_time, > LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lead > FROM truck_loc > -- WHERE EXTRACT(EPOCH FROM the_lead_2) - EXTRACT(EPOCH FROM the_lead) < 3600 > ORDER BY loc_date_time; > > > > <pre> > truck_id | location_name | loc_date_time | the_lead > :----------- | :------------ | :------------------ | :------------------ > MonsterTruck | Home | 2019-10-22 10:27:40 | 2019-10-22 10:27:40 > MonsterTruck | Home | 2019-10-22 10:27:40 | 2019-10-22 10:33:10 > MonsterTruck | Home | 2019-10-22 10:33:10 | 2019-10-22 11:22:40 > MonsterTruck | Home | 2019-10-22 11:22:40 | 2019-10-22 11:22:40 > MonsterTruck | Home | 2019-10-22 11:22:40 | 2019-10-22 16:43:00 > MonsterTruck | Home | 2019-10-22 11:22:40 | 2019-10-22 11:22:40 > MonsterTruck | Gas Station | 2019-10-22 12:43:00 | 2019-10-22 13:13:00 > MonsterTruck | Gas Station | 2019-10-22 13:13:00 | 2019-10-22 13:43:00 > MonsterTruck | Gas Station | 2019-10-22 13:43:00 | <em>null</em> > MonsterTruck | Home | 2019-10-22 16:43:00 | 2019-10-22 16:49:00 > MonsterTruck | Home | 2019-10-22 16:49:00 | 2019-10-22 17:43:00 > MonsterTruck | Home | 2019-10-22 17:43:00 | 2019-10-22 20:43:00 > MonsterTruck | Home | 2019-10-22 20:43:00 | 2019-10-22 20:56:00 > MonsterTruck | Home | 2019-10-22 20:56:00 | <em>null</em> > </pre> <!-- --> > -- Leading boundary > > SELECT t1.truck_id, t1.location_name, t1.loc_date_time > FROM > ( > SELECT > truck_id, > location_name, > loc_date_time, > LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id ORDER BY loc_date_time) AS the_lead > FROM truck_loc > ) AS t1 > WHERE t1.the_lead >= t1.loc_date_time + INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530 > OR t1.the_lead IS NULL -- Last lead is always NULL > ORDER BY t1.loc_date_time; > > <pre> > truck_id | location_name | loc_date_time > :----------- | :------------ | :------------------ > MonsterTruck | Home | 2019-10-22 11:22:40 > MonsterTruck | Gas Station | 2019-10-22 13:43:00 > MonsterTruck | Home | 2019-10-22 17:43:00 > MonsterTruck | Home | 2019-10-22 20:56:00 > </pre> <!-- --> > -- Lagging boundary > > SELECT SUBSTR(t1.truck_id, 1, 3), t1.location_name, t1.loc_date_time, > EXTRACT(EPOCH FROM t1.loc_date_time) AS t, EXTRACT(EPOCH FROM t1.the_lag) AS tlag, > EXTRACT(EPOCH FROM t1.loc_date_time) - EXTRACT(EPOCH FROM t1.the_lag) AS diff > FROM > ( > SELECT > truck_id, > location_name, > loc_date_time, > LAG (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lag > FROM truck_loc > ) AS t1 > WHERE t1.the_lag <= t1.loc_date_time - INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530 > OR t1.the_lag IS NULL -- First lag is always NULL > ORDER BY t1.loc_date_time; > > <pre> > substr | location_name | loc_date_time | t | tlag | diff > :----- | :------------ | :------------------ | :--------- | :--------- | :---- > Mon | Home | 2019-10-22 10:27:40 | 1571740060 | <em>null</em> | <em>null</em> > Mon | Gas Station | 2019-10-22 12:43:00 | 1571748180 | <em>null</em> | <em>null</em> > Mon | Home | 2019-10-22 16:43:00 | 1571762580 | 1571743360 | 19220 > Mon | Home | 2019-10-22 20:43:00 | 1571776980 | 1571766180 | 10800 > </pre> <!-- --> > WITH cte1 AS > ( > SELECT t1.truck_id, t1.location_name, t1.loc_date_time > FROM > ( > SELECT > truck_id, > location_name, > loc_date_time, > LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id ORDER BY loc_date_time) AS the_lead > FROM truck_loc > ) AS t1 > WHERE t1.the_lead >= t1.loc_date_time + INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530 > OR t1.the_lead IS NULL > ORDER BY t1.loc_date_time > ), > cte2 AS > ( > SELECT t1.truck_id, t1.location_name, t1.loc_date_time, > EXTRACT(EPOCH FROM t1.loc_date_time) AS t, EXTRACT(EPOCH FROM t1.the_lag) AS tlag, > EXTRACT(EPOCH FROM t1.loc_date_time) - EXTRACT(EPOCH FROM t1.the_lag) AS diff > FROM > ( > SELECT > truck_id, > location_name, > loc_date_time, > LAG (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lag > FROM truck_loc > ) AS t1 > WHERE t1.the_lag <= t1.loc_date_time - INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530 > OR t1.the_lag IS NULL > ORDER BY t1.loc_date_time > ) > SELECT > SUBSTR(c1.truck_id, 1, 3) AS tid_1, SUBSTR(c2.truck_id, 1, 3) AS tid_2, > SUBSTR(c1.location_name, 1, 3) as loc_1, SUBSTR(c2.location_name, 1, 3) AS loc_2, > c2.loc_date_time AS ldt_1, c1.loc_date_time AS ldt_2, > (EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) AS diff > FROM cte1 c1 > JOIN cte2 c2 > ON c1.truck_id = c2.truck_id AND > c1.location_name = c2.location_name > WHERE ABS(EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) <= 3600 > ORDER BY c2.loc_date_time; > > <pre> > tid_1 | tid_2 | loc_1 | loc_2 | ldt_1 | ldt_2 | diff > :---- | :---- | :---- | :---- | :------------------ | :------------------ | :--- > Mon | Mon | Hom | Hom | 2019-10-22 10:27:40 | 2019-10-22 11:22:40 | 3300 > Mon | Mon | Gas | Gas | 2019-10-22 12:43:00 | 2019-10-22 13:43:00 | 3600 > Mon | Mon | Hom | Hom | 2019-10-22 16:43:00 | 2019-10-22 17:43:00 | 3600 > Mon | Mon | Hom | Hom | 2019-10-22 20:43:00 | 2019-10-22 20:56:00 | 780 > </pre> <!-- --> > -- This is strange and the reason I changed to EPOCH - there is clearly a difference > -- of more than one hour between the two TIMESTAMPS. > > SELECT '2019-10-22 11:22:40'::TIMESTAMP - '2019-10-22 20:43:00'::TIMESTAMP < INTERVAL '1 HOUR' > > <pre> > | ?column? | > | :------- | > | t | > </pre> <!-- --> > SELECT EXTRACT(EPOCH FROM '2019-10-22 11:22:40'::TIMESTAMP) - EXTRACT(EPOCH FROM '2019-10-22 20:43:00'::TIMESTAMP); > > <pre> > | ?column? | > | :------- | > | -33620 | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=98a9c02fbe15ae405aff5501cdb2f3fd)*
back to fiddle