By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (40624 in the last week).
select version();
version
PostgreSQL 12.7 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 TIMESTAMPTZ(0) 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)
SELECT * FROM test;
the_date
2021-06-23 05:47:05+01
2021-06-23 07:47:05+01
2021-06-23 09:47:05+01
2021-06-23 11:47:05+01
2021-06-23 13:47:05+01
2021-06-23 14:47:05+01
2021-06-23 16:47:05+01
2021-06-23 17:47:05+01
2021-06-24 05:47:05+01
2021-06-24 09:47:05+01
2021-06-24 13:47:05+01
2021-06-24 14:47:05+01
2021-06-24 16:47:05+01
2021-06-24 17:47:05+01
…
hidden batch(es)
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;
dat
td
l_td
diff
2021-06-23
2021-06-23 05:47:05+01
2021-06-23 07:47:05+01
02:00:00
2021-06-23
2021-06-23 07:47:05+01
2021-06-23 09:47:05+01
02:00:00
2021-06-23
2021-06-23 09:47:05+01
2021-06-23 11:47:05+01
02:00:00
2021-06-23
2021-06-23 11:47:05+01
2021-06-23 13:47:05+01
02:00:00
2021-06-23
2021-06-23 13:47:05+01
2021-06-23 14:47:05+01
01:00:00
2021-06-23
2021-06-23 14:47:05+01
2021-06-23 16:47:05+01
02:00:00
2021-06-23
2021-06-23 16:47:05+01
2021-06-23 17:47:05+01
01:00:00
2021-06-23
2021-06-23 17:47:05+01
2021-06-24
2021-06-24 05:47:05+01
2021-06-24 09:47:05+01
04:00:00
2021-06-24
2021-06-24 09:47:05+01
2021-06-24 13:47:05+01
04:00:00
2021-06-24
2021-06-24 13:47:05+01
2021-06-24 14:47:05+01
01:00:00
2021-06-24
2021-06-24 14:47:05+01
2021-06-24 16:47:05+01
02:00:00
2021-06-24
2021-06-24 16:47:05+01
2021-06-24 17:47:05+01
01:00:00
2021-06-24
2021-06-24 17:47:05+01
…
hidden batch(es)
--
-- 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';'
dat
td
diff
2021-06-24
2021-06-24 05:47:05+01
04:00:00
2021-06-24
2021-06-24 09:47:05+01
04:00:00
…
hidden batch(es)
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;
dat
min
2021-06-23
2021-06-23 17:47:05+01
2021-06-24
2021-06-24 05:47:05+01
…
hidden batch(es)
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
The date
Gap start or last ts
2021-06-23
2021-06-23 17:47:05+01
2021-06-24
2021-06-24 05:47:05+01
…
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 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 ;
The date
Gap start or last ts
2021-06-23
2021-06-23 17:47:05+01
2021-06-24
2021-06-24 05:47:05+01
…
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+01
2021-06-24
2021-06-24 05:47:05+01
…
hidden batch(es)
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 ;
