Skip to content

Instantly share code, notes, and snippets.

@binarycreations

binarycreations/with_sqlite_missing_dates.sql

Created November 23, 2015 21:51
Show Gist options
  • Save binarycreations/45296e15c11e55cd5816 to your computer and use it in GitHub Desktop.
Save binarycreations/45296e15c11e55cd5816 to your computer and use it in GitHub Desktop.
Download ZIP
Find missing entries within a sequence of data
Raw
with_sqlite_missing_dates.sql
WITH RECURSIVE
days(day) AS (
SELECT '2015-10-05T00:00:00Z'
UNION ALL
SELECT date(day,'+1 day') || 'T00:00:00Z' FROM days WHERE day < '2015-11-21T00:00:00Z'
)
SELECT day, picks.title, picks.date FROM days LEFT JOIN picks ON picks.date = days.day WHERE picks.date IS NULL;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment