Select last 24 hours from SQlite where EPOCH time used

Question

I am trying to select all data from an SQlite database for the last 24 hours. There is a column 'Date' where the date is present and that is in EPOCH time. I've tried a variety of different commands but this seems to be what I have so far:

SELECT * 
FROM Log
WHERE UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL -1 DAY))

and

SELECT * FROM Log WHERE Date >= datetime('now', ' '-24 hours');

This doesn't seem to work. Ultimately, I am trying to convert the last 24 hours of this database to a CSV in Unix and have only the last 24 hours there using the following:

sqlite3 -header -csv /opt/demo/log_20170501131627.db " select * from Log 
WHERE Date >= datetime('now', ' '-24 hours');" > /opt/demo/DB.csv

Any ideas?

Answer 1

SELECT * FROM Log WHERE Date >= strftime('%s', 'now', '-1 day');

You can try it here:

http://sqlfiddle.com/#!5/440d5/2/0

edit: simplified strftime usage based on "CL."-s comment below (thanks)

Answer 2

I'm not sure the precision of your epoch time. In case you are saving your epoch time to the millisecond, you can do it like this:

SELECT * FROM Log WHERE CAST((DATE/1000) AS LONG) >= strftime('%s', 'now', '-1 day');

The divide by 1000 above is just converting it to seconds and then casting it to a long (decimal values don't work if you are using '%s'). So if your epoch time is in seconds, just ignore the cast and division part of the above solution. Hope this helps!