List the Missing Dates in a Date Series

Posted October 22, 2020 by Jim Knicely, Vertica Field Chief Technologist

The TIMESERIES clause in Vertica makes it easy to list all of the gap days (that is, those that are missing) from a series of dates.

Say I have this simple date series:

dbadmin=> SELECT some_date
dbadmin-> FROM dates
dbadmin-> ORDER BY some_date;
some_date
------------
2020-10-20
2020-10-21
2020-10-22
2020-10-23
2020-10-24
2020-10-25
2020-10-26
2020-10-27
2020-10-28
2020-10-29
2020-10-31
2020-11-01
2020-11-05
(13 rows)

As you can see, there are several dates missing from the series (2020-10-30, 2020-11-02, 2020-11-03 and 2020-11-04).

I can list these absent dates via a SQL query which uses the handy TIMESERIES clause like so:

dbadmin=> SELECT slice_time::DATE missing_dates
dbadmin-> FROM dates
dbadmin-> TIMESERIES slice_time AS '1 Day' OVER (ORDER BY some_date::TIMESTAMP)
dbadmin-> MINUS
dbadmin-> SELECT some_date
dbadmin-> FROM dates;
missing_dates
---------------
2020-10-30
2020-11-02
2020-11-03
2020-11-04
(4 rows)

Helpful link:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/SELECT/TIMESERIESClause.htm

Have fun!