Code. SQLite. Time tables with recursive CTE

#sqlite #datetime #cte

Hi!

Assuming you know what time tables are and you need to have them in SQLite, here's how we can generate one:

WITH RECURSIVE
  cnt(x) AS (
    VALUES(unixepoch('2025-01-01 00:00:00'))    --> Start datetime.
    UNION ALL
    SELECT x + 60                               --> Period duration, in seconds.
    FROM cnt
    WHERE x < unixepoch('2025-01-01 03:00:00')  --> End datetime.
  )

SELECT
  x AS start,
  x + 59 AS stop                                --> Also period duration, minus last second.
FROM cnt;

The code above will generate 181 rows of start - stop pairs since 2025-01-01 00:00:00 till 2025-01-01 03:00:00. Each pair represents a 1-minute interval (left end included, right end excluded). Obviously, you can change the interval to be anything, just don't forget to update it in both places.

Full example with saving to an actual time table:

CREATE TABLE periods_1m (start INTEGER PRIMARY KEY, stop INTEGER);

WITH RECURSIVE
  cnt(x) AS (
    VALUES(unixepoch('2025-01-01 00:00:00'))
    UNION ALL
    SELECT x + 60
    FROM cnt
    WHERE x < unixepoch('2025-01-01 03:00:00')
  )

INSERT INTO periods_1m (start, stop)
SELECT x, x + 59
FROM cnt;