sqlite3 date and interval functions

问题:

I wonder whether sqlite3 supports interval function. The following statement is accepted by PostgreSQL, however sqlite3 failed to parse it;

select
 ...
from 
 orders
where
 ...
 and o_orderdate < date '1995-03-01' + interval '3' month 

Error: near line 4: near "'1995-03-01'": syntax error

Then, I modified the statement a little bit such as;

and o_orderdate < date('1995-03-01') + interval '3' month

This time the error was; Error: near line 4: near "'3'": syntax error

Unfortunately, same trick did not work for the interval function i.e.

and o_orderdate < date('1995-03-01') + interval('3' month)

or

and o_orderdate < date('1995-03-01') + interval('3') month

or even

and o_orderdate < date('1995-03-01') + interval(3 month)

still gave me the syntax error.

Maybe sqlite3 does not support interval function or am I missing something in its usage?

Thanks a lot

回答1:

and o_orderdate < date('1995-03-01', '+3 month')

reference of date and time functions in SQLite

回答2:

try this:

and date(o_orderdate) < date('1995-03-01','+3 month')

In order to calculate dates, you must inform to SQLite that your database field is a date: date(o_orderdate)

SQLite syntax is pretty unflexible, so it's important to make sure that the + sign has no spaces to the following number: '+3 month'