SQLite Forum

select between two dates
Login

select between two dates

(1) By anonymous on 2020-05-06 09:31:07 [link] [source]

I have a sqlite3 database from which i wish to extract date between 14 days before the query.
my time data is held as interger secs in col ID and i can use the BETWEEN selection if i give the req as like '5900000'
 and '7777777'   note the ' s.
 however 'now'-12 days and 'now'
 or it many possibilities dosnt work
actual line is 
for row in cursor.execute(!SELECT * FROM env3 WHERE ID BETWEEN 'now'-(14*24*12*60*^) AND 'now'");

 HELP PLEASE

(2) By Gunter Hick (gunter_hick) on 2020-05-06 10:20:25 in reply to 1 [link] [source]

'now' is a string because it is in single quotes. Also, your numeric literals are actually strings. Luckily SQLite will apply affinity before comparing.

You need to use the strftime() function to convert between time strings and "seconds since 1970-01-01".

(3) By Tony Papadimitriou (tonyp) on 2020-05-06 14:31:47 in reply to 1 [link] [source]

date('now','-14 days') will give you the date 14 days ago.

(4) By Keith Medcalf (kmedcalf) on 2020-05-06 14:43:32 in reply to 1 [source]

strftime('%s', 'now', '-14 days') yields the unix epoch second count 14 days prior to now (the time the statement commenced execution) as a character string (not as an integer).

strftime('%s') yields the now (the time the statement commenced execution) second count since the unix epoch as a character string (not as an integer).