Billy Fung

Finding missing dates

Billy Fung / 2018-03-13


Often when dealing with time series data, and ingesting it on a daily basis you want to make sure that everything is there. The easiest way to do this is checking to see if the date value is continuous. With PostgreSQL, the generate_series function makes this quite easy.

with calendar as (
    select ((select min(nzdate) from table_a)::date + (n || ' days')::interval)::date cal_date
    from generate_series(0, (select max(nzdate) - min(nzdate) from table_a)) n
)
select cal_date
from calendar c
left join table_a t on t.nzdate = c.cal_date
where t.nzdate is null;