Tag Info

New answers tagged


This query returns all gaps in the data that overlap with the given time range. Also covers leading, trailing or total (covering) gaps: WITH input(t1, t2) AS (SELECT '2015-04-02 22:00'::timestamp , '2015-04-02 23:00'::timestamp) -- t1 < t2 , gap AS ( SELECT max(end_date) OVER (ORDER BY start_date ROWS ...


First, we combine intervals that overlap to find all the contiguous "islands" of the intervals: with c as ( select *, max(end_date) over (order by start_date rows between unbounded preceding and 1 preceding) as previous_max from my_table ) select start_date, ...

Top 50 recent answers are included