Menu
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.
259
To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime ) and cast it to date . In SQL Server, you can subtract or add any number of days using the DATEADD() function. The DATEADD() function takes three arguments: datepart , number , and date .
To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.
In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column.
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';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
