PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Number of missing periods between dates

I’m using Postgres and I would like to find missing range of dates. I’ve got this table with these data :

create table event_dates
(
    date date
);

INSERT INTO event_dates (date) VALUES ('2024-12-09');
INSERT INTO event_dates (date) VALUES ('2024-12-13');
INSERT INTO event_dates (date) VALUES ('2024-12-20');

I would like to find the number of missing periods between an arbitrary range. For example,
between 2024-12-05 and 2024-12-25 I would like the result to be 4, because :

  • no dates between 2024-12-05 and 2024-12-09 (first gap)
  • no dates between 2024-12-09 and 2024-12-13 (second gap)
  • no dates between 2024-12-13 and 2024-12-20 (third gap)
  • no dates between 2024-12-20 and 2024-12-25 (fourth gap)

I can’t get it work with ant window function.

Any clues ?

Thanks,

Tags:

2

Answers


  1. 0

    See example

    date
    2024-12-09
    2024-12-13
    2024-12-20 
    select min(date) min_date,max(date) max_date
  ,sum(isGap) countGaps
from(
  select *
    ,case when lead(date,1,date)over(order by date)=date then 0 else 1 end isGap
  from(
    select date
    from event_dates
    where date between '2024-12-05' and '2024-12-25'
    union all 
    select '2024-12-05'::date date 
    union all 
    select '2024-12-25'::date
  ) a
)b
    min_date max_date countgaps
    2024-12-05 2024-12-25 4

    fiddle

    Login or Signup to reply.
  2. 0

    You may first create a list of all dates within the target range (use generate_series), then left join event_dates to it and then discover which dates are start of a missing period using window function lag.

    select sum(case when coalesce(range_start, true) then 1 end)
from 
(
  select s::date, (s::date - lag(s::date) over (order by s::date) > 1) as range_start
  from generate_series('2024-12-05', '2024-12-25', interval '1 day') as s
  left outer join event_dates ed on s = ed.date
  where ed.date is null
) t;

    DBV-Fiddle demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search