I am trying to select a table of every date for the last year.

In SQL Server, I can run something like this:

SELECT TOP (DATEDIFF(DAY, DATEADD(YEAR, - 1, GETDATE()), GETDATE())   1) 
    Date = CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, DATEADD(YEAR, - 1, GETDATE())) AS DATE) 
FROM sys.all_objects a

It returns 1 column with 366 rows containing the dates from 1 year ago until now.

I am looking for something equivalent in SQLITE.

CodePudding user response：

You can do it with a recursive CTE:

WITH cte AS (
  SELECT DATE(CURRENT_DATE, '-1 year') date
  UNION ALL
  SELECT DATE(date, ' 1 day') 
  FROM cte
  WHERE date < CURRENT_DATE
)
SELECT * FROM cte;

See the demo.

CodePudding user response：

You can also do this using an inline tally table

WITH L1(n) AS (
  VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
),
L2(n) AS (
  SELECT 1 FROM L1 a CROSS JOIN L1 b
)
SELECT DATE(CURRENT_DATE, (-ROW_NUMBER() OVER (ORDER BY 1)   1) || ' days') AS date
FROM L2
LIMIT julianday(CURRENT_DATE) - julianday(DATE(CURRENT_DATE, '-1 year'))   1;

db<>fiddle

For newer versions of SQLite you may want to add NOT MATERIALIZED to the CTEs.