Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
view_every_hour_past_six_months.sql
DROP VIEW IF EXISTS view_every_hour_past_six_months;
CREATE VIEW view_every_hour_past_six_months AS
WITH RECURSIVE
bounds AS (
SELECT
strftime('%s', 'now', 'start of day', '-6 month') AS lower,
strftime('%s', 'now', 'start of day', '+1 day') AS upper
),
samples AS (
SELECT lower AS sample FROM bounds
UNION
SELECT
strftime('%s', sample, 'unixepoch', '+1 hour') AS sample
FROM
samples
INNER JOIN bounds
WHERE
strftime('%s', sample, 'unixepoch', '+1 hour') < bounds.upper
)
SELECT
sample AS unixepoch,
strftime('%Y-%m-%dT%H:%M:%S', sample, 'unixepoch') AS iso
FROM
samples
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.