Difference between current date and date stored in database doesn't work
I have a table that contains a column named timestamp
that stores the generated datetime entries in seconds (converting datetime value from Date () using strtotime ()). I need to get records whose creation date is more than 72 hours in order to delete those records.
My table has the following columns:
userid(int)
timestamp(storing created dates in seconds)eg:'1427886372'
I used the following query
SELECT DATEDIFF(HOUR,timestamp,'current date in seconds')>72 FROM table_name.
Does not work. I would also like to know if there is an option to convert the result GETDATE()
to seconds in sql as strtotime()
in php, since I am getting the current date using php Date()
and converting it to seconds using strtotime()
.
** EDIT: ** According to kba's answer, I modified the request as follows.
SELECT * FROM PRTL_UsrHashKeys where
DATEDIFF(HOUR,timestamp, DATEDIFF(second, '1970-01-01', GETDATE()))>72
But his show An expression of non-boolean type specified in a context where a condition is expected, near ')'.
source to share
You can convert the output of GETDATE () to a unix timestamp with a simple statement:
SELECT GETDATE(), DATEDIFF(second, '1970-01-01', GETDATE())
You can check the result with a different timestamp source (like some PHP function). The reverse is a little trickier.
How to convert bigint (UNIX timestamp) to datetime in SQL Server?
Why are you using unix timestamp on SQL server? She has no support. Funtions DATEDIFF, DATEADD, etc. Only works with real dates, not unix timestamps.
source to share