Get Rows Where timestamp Column is 2 days Old

问题:

Hey guys, I have a table that has a column with the timestamp in sqlite3. It is default to the CURRENT_TIMESTAMP such that when the row gets inserted, the current time is inserted. Now I am trying to fetch the rows that have been inserted 2 days ago or more. I wonder if that makes any sense.

Reading the documentation I came up with:

SELECT * FROM test WHERE timestamp < strftime('%s', '-2 days')

but apparently that's wrong. I came up with this query because that is similar to the way in which I am doing the test in my actual code:

strtotime($timestamp) < strtotime("-2 days").

But I was hoping that sqlite3 included some built-in checks for this type of situation.

Thanks, I appreciate any responses.

EDIT: Figured it out: SELECT * FROM test WHERE timestamp < date('now', '-2 days')

I'll keep this open in case someone can come up with something better.

回答1:

in postgres (i know it is not your platform, but i am posting here for others' reference), you can also do the following:

SELECT * FROM test WHERE my_timestamp < NOW() - INTERVAL '2 DAY';

回答2:

So I think I figured it out, it works fine for me. I don't know if this is the best way of doing it, but seems pretty straightforward and like I said, works:

SELECT * FROM test WHERE timestamp < date('now', '-2 days')

回答3:

SELECT * FROM test WHERE timestamp <= datetime('now','-2 days')

where data format should be in one of following formats as mentioned in link

https://www.sqlite.org/lang_datefunc.html