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. 2335853 fiddles created (27449 in the last week).

select version();
version
PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
 hidden batch(es)


CREATE TABLE test ( the_date TIMESTAMP NOT NULL );
 hidden batch(es)


INSERT INTO test VALUES ('2021-06-23 05:47:05'::TIMESTAMPTZ), -- NO gaps > 3 hours on this date! ('2021-06-23 07:47:05'::TIMESTAMPTZ), ('2021-06-23 09:47:05'::TIMESTAMPTZ), ('2021-06-23 11:47:05'::TIMESTAMPTZ), ('2021-06-23 13:47:05'::TIMESTAMPTZ), ('2021-06-23 14:47:05'::TIMESTAMPTZ), ('2021-06-23 16:47:05'::TIMESTAMPTZ), ('2021-06-23 17:47:05'::TIMESTAMPTZ), ('2021-06-24 05:47:05'::TIMESTAMPTZ), -- TWO gaps > 3 hours on this date -- 1st gap > 3 hours ('2021-06-24 09:47:05'::TIMESTAMPTZ), -- 2nd gap > 3 hours ('2021-06-24 13:47:05'::TIMESTAMPTZ), ('2021-06-24 14:47:05'::TIMESTAMPTZ), -- added for testing ('2021-06-24 16:47:05'::TIMESTAMPTZ), -- added for testing ('2021-06-24 17:47:05'::TIMESTAMPTZ);
14 rows affected
 hidden batch(es)


WITH long_gaps AS ( SELECT dat, MIN(td) FROM ( SELECT the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER (PARTITION BY the_date::DATE) AS l_td FROM test ) AS t1 WHERE l_td - td > INTERVAL '3 HOUR' GROUP BY dat ), short_gaps AS ( SELECT the_date::DATE AS dat2, MAX(the_date) FROM test WHERE the_date::DATE NOT IN (SELECT dat FROM long_gaps) GROUP BY dat2 ) SELECT * FROM long_gaps UNION ALL SELECT * FROM short_gaps ORDER BY dat;
dat min
2021-06-23 2021-06-23 17:47:05
2021-06-24 2021-06-24 05:47:05
 hidden batch(es)


WITH long_gaps AS ( SELECT the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER (PARTITION BY the_date::DATE) AS l_td FROM test ) SELECT DISTINCT ON(dat) dat, td FROM long_gaps WHERE l_td - td > INTERVAL '3 HOUR' OR l_td IS NULL ORDER BY dat, td ;
dat td
2021-06-23 2021-06-23 17:47:05
2021-06-24 2021-06-24 05:47:05
 hidden batch(es)


WITH gaps AS ( SELECT the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER (PARTITION BY the_date::DATE) AS l_td FROM test ), long_gaps AS ( SELECT dat, td, ROW_NUMBER() OVER (PARTITION BY dat ORDER BY td) AS rn FROM gaps WHERE l_td - td > INTERVAL '3 HOUR' OR l_td IS NULL ) SELECT dat, td FROM long_gaps WHERE rn = 1 ORDER BY dat, td ;
dat td
2021-06-23 2021-06-23 17:47:05
2021-06-24 2021-06-24 05:47:05
 hidden batch(es)