Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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


CREATE TABLE test
(
  the_date TIMESTAMP 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);
14 rows affected

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


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


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