从使用EPOCH时间的SQlite中选择最后24小时

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:

我试图在过去的24小时内从SQlite数据库中选择所有数据。有一个列'日期'，其中存在日期，这是在EPOCH时间。我尝试了各种不同的命令，但这似乎是我到目前为止所做的：

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:

这似乎不起作用。最后，我试图将这个数据库的最后24小时转换为Unix中的CSV，并且在过去的24小时内只使用以下内容：

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

Any ideas?

有任何想法吗？

2 个解决方案

#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)

编辑：基于“CL。”的简化strftime用法 - 下面的评论（谢谢）

#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!

上面的除以1000只是将其转换为秒，然后将其转换为长（如果您使用'％s'，则十进制值不起作用）。因此，如果您的纪元时间以秒为单位，则忽略上述解决方案的强制转换部分。希望这可以帮助！

#1

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