view_every_hour_past_six_months.sql

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