select version();

CREATE TABLE test ( the_date TIMESTAMPTZ(0) NOT NULL );

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);

SELECT * FROM test;

SELECT the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER (PARTITION BY the_date::DATE ORDER BY the_date ASC) AS l_td, LEAD(the_date) OVER (PARTITION BY the_date::DATE ORDER BY the_date ASC) - the_date AS diff FROM test ORDER BY dat, td;

-- -- Note - only records from 2021-04-23 - none from 2021-06-23 -- SELECT dat, td, l_td - td AS diff 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'; -- This is for gaps **_strictly_** larger than 3 hours - if you want greater -- than or equal to 3 hours, then use '>= INTERVAL '3 HOUR';'

SELECT dat, MIN(td) FROM ( SELECT the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER () AS l_td FROM test ) AS t1 WHERE l_td - td > INTERVAL '3 HOUR' GROUP BY dat;

WITH long_gaps AS ( SELECT dat, MIN(td) AS gap FROM ( SELECT the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER () 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 dat AS "The date", gap AS "Gap start or last ts" FROM long_gaps UNION SELECT * FROM short_gaps ORDER BY 1; -- parameter 1 which ORDERs BY the first field in the query -- ORDER BY "The date"; -- you can do this if you find it more legible

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 AS "The date", td AS "Gap start or last ts" FROM long_gaps WHERE l_td - td > INTERVAL '3 HOUR' OR l_td IS NULL ORDER BY dat, td ;

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 ;

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS) WITH long_gaps AS ( SELECT dat, MIN(td) AS gap FROM ( SELECT the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER () 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 dat AS "The date", gap AS "Gap start or last ts" FROM long_gaps UNION SELECT * FROM short_gaps ORDER BY 1; -- parameter 1 which ORDERs BY the first field in the query

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS) 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 AS "The date", td AS "Gap start or last ts" FROM long_gaps WHERE l_td - td > INTERVAL '3 HOUR' OR l_td IS NULL ORDER BY dat, td ;

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS) 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 ;