Creating a date range table
sql, sqlite
Solution
Here's how you could use a numbers table to expand a range:
SELECT
datetime('now', N || ' seconds') AS DT
FROM numbers
WHERE N < strftime('%s', 'now', '1 minutes') - strftime('%s', 'now');
In this case, the numbers table is supposed to hold numbers starting from 0.
A numbers table is a tool worth keeping handy for many purposes. You could initialise it like this, for instance:
CREATE TABLE numbers (N int);
/* #0 */ INSERT INTO numbers (N) SELECT 0;
/* #1 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #2 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #3 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #4 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #5 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #6 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #… */
Every line `#N` results in 2N rows in the table, the largest number being `2N-1`
A demonstration of the method can be found (and played with) on SQL Fiddle.
Problem
I'm writing a report that needs to display a value per day. I have the start and end date for the query, but I wish to avoid missing days in case the table does not contain a value for a specific date. I was thinking about creating a base date range table that holds all days between start and end, then left join it with the data table to show a value for each day. I found a few scripts for mySQL, SQL Server, etc.. but none for SQLite. Is there a way to quickly populate a table with a date range? Thanks