Convert query from MySql to Sqlite

Question

I need to convert this query from MySQL format to SQLite. I'm trying myself but I've found some difficulty.

In SQLite, the curdate() and the interval functions do not exist.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24'

CL. · Accepted Answer · 2014-05-05 06:29:44Z

2

What this query actually does is just generating lots of consecutive dates (up to one thousand previous days).

In SQLite 3.8.3 or later, this can be done more easily with a recursive common table expression:

WITH RECURSIVE dates(d)
AS (VALUES('2010-01-20')
    UNION ALL
    SELECT date(d, '+1 day')
    FROM dates
    WHERE d < '2010-01-24')
SELECT d AS date FROM dates;

answered May 5, 2014 at 6:29

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3449772 Over a year ago

Thanks man!! you're awesome!! :) I've checked the previous answer because he has answered my question but I think that your solution is better than my and I will take for my project! :)

Gordon Linoff · Accepted Answer · 2014-05-04 23:10:27Z

1

Here is the basic syntax:

select a.Date 
from (select date('now', '-'||(a.a + (10 * b.a) + (100 * c.a))|| ' days') as Date
      from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
           ) a cross join
           (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
           ) b cross join
           (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
           ) c
    ) a;

I left out the where clause because those dates are more than 1000 days in the past, so they won't select anything anyway.

answered May 4, 2014 at 23:10

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

user3449772 Over a year ago

Thanks!! I've checked your answer but I've decided to substitute the query with the query on the next answer because is better for me. :)

Collectives™ on Stack Overflow

Convert query from MySql to Sqlite

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related