clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1044308 fiddles created (9412 in the last week).

select version();
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
 hidden batch(es)


CREATE TABLE truck_loc ( truck_id VARCHAR(50), location_name VARCHAR(12), loc_date_time TIMESTAMP );
 hidden batch(es)


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');
14 rows affected
 hidden batch(es)


-- SELECT * FROM truck_loc;
 hidden batch(es)


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;
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
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
 hidden batch(es)


-- 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;
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
 hidden batch(es)


-- 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;
substr location_name loc_date_time t tlag diff
Mon Home 2019-10-22 10:27:40 1571740060
Mon Gas Station 2019-10-22 12:43:00 1571748180
Mon Home 2019-10-22 16:43:00 1571762580 1571743360 19220
Mon Home 2019-10-22 20:43:00 1571776980 1571766180 10800
 hidden batch(es)


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;
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
 hidden batch(es)


-- 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'
?column?
t
 hidden batch(es)


SELECT EXTRACT(EPOCH FROM '2019-10-22 11:22:40'::TIMESTAMP) - EXTRACT(EPOCH FROM '2019-10-22 20:43:00'::TIMESTAMP);
?column?
-33620
 hidden batch(es)