Code. SQLite. Time tables with recursive 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; 

Resources:

• SQLite docs on WITH clause and recursive CTE
• blog post by Brady Holt, which pushed me to finally read the SQLite docs above (:

Bye!

P.S.
Nice drawing canvas, Brady! Such touches make the web alive)