List the Missing Dates in a Date Series

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!