Search code examples
sqlitedatetimewhere-clauseunix-timestamp

SQLite Unix timestamp getting dates older than 2 days ago

I have a field in my SQLite DB called created_date and I want to return all the results that are greater then 2 days ago.

I run this:

SELECT strftime('%Y-%m-%d %H:%M:%S', "created_date"/1000, 'unixepoch') FROM Logs

I get the results showing in proper datetime format but when I add this to the end:

where created_date <= datetime('now','-2 day')

all 10 results return.

created_date, created_date as datetime
1552143373, 2019-03-09 21:44:55 55.000
1552143373, 2019-03-09 21:44:55 55.000
1552143373, 2019-03-09 21:27:38 38.000
1552143373, 2019-03-09 21:29:19 19.000
1552143373, 2019-03-09 21:30:06 06.000
1552143373, 2019-03-09 21:30:10 10.000
1552143373, 2019-03-09 21:41:28 28.000
1552143373, 2019-03-09 21:42:40 40.000
1552143373, 2019-03-09 21:43:25 25.000
1552143373, 2019-03-09 21:50:07 07.000
Solution

  • datetime() function returns "YYYY-MM-DD HH:MM:SS" - https://www.sqlite.org/lang_datefunc.html

    So use strftime('%s','now','-2 days') to get an integer'ish string for comparison.