4

I have a table which contain a column named timestamp which stores created datetime of the record in seconds(convert the datetime value from Date() using strtotime()). I need to get the records whose created date is greater than 72 hours in order to delete those records.

My table has following columns:

userid(int)
timestamp(storing created dates in seconds)eg:'1427886372'

I have used the following query

SELECT DATEDIFF(HOUR,timestamp,'current date in seconds')>72 FROM table_name.

Its not working. I would also like to know, if there is any option to convert the result of GETDATE() to seconds in sql just like 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 have modified the query like this.

SELECT * FROM PRTL_UsrHashKeys where 
DATEDIFF(HOUR,timestamp, DATEDIFF(second, '1970-01-01', GETDATE()))>72

But its showing An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Techy
  • 2,626
  • 7
  • 41
  • 88
  • 1
    `1427886372` is created date in seconds starting from which date `1900-01-01`? – ughai Apr 20 '15 at 11:00
  • @ughai I have used the Date() in php to get the date and used strtotime() to convert the result to seconds. – Techy Apr 20 '15 at 11:03
  • 1
    Use `WHERE [Timestamp] < DATEDIFF(SECOND, '19700101', DATEADD(HOUR, -72, GETDATE()))`, i.e. where the timestamp is less than 72 hours ago, rather than performing functions on the timestamp column. If it is not too late you may wish to consider switching to the `DATETIME` type, or `DATETIME2` if you are using 2008 or later. It will make queries easier in the long run and avoid all these conversions. – GarethD Apr 20 '15 at 11:21
  • I need the records which are created 72 hours before the current date. @GarethD – Techy Apr 20 '15 at 11:25
  • @GarethD If you give the comment as answer I will accept your answer – Techy Apr 20 '15 at 11:27

2 Answers2

3

You can convert output of GETDATE() to unix timestamp by simple statement:

SELECT GETDATE(), DATEDIFF(second, '1970-01-01', GETDATE())

You can check the result with another source of timestamp (for example some PHP function). The reverse is a little bit harder.

UNIX_TIMESTAMP in SQL Server

How can I convert bigint (UNIX timestamp) to datetime in SQL Server?

Why do you use unix timestamp in SQL server? It does not have support for that. Funtions DATEDIFF, DATEADD etc. works only with real dates, not with unix timestamp seconds.

Community
  • 1
  • 1
kba
  • 4,190
  • 2
  • 15
  • 24
  • So in my case how can I use the query.I have used like this: SELECT * FROM PRTL_UsrHashKeys where DATEDIFF(HOUR,timestamp, DATEDIFF(second, '1970-01-01', GETDATE()))>72 – Techy Apr 20 '15 at 11:17
  • I have modified my query.Can you help me how to get me the result – Techy Apr 20 '15 at 11:21
  • 1
    You can not use SQL server functions with unix timestamp. It does not work. Try something like this `SELECT * FROM PRTL_UsrHashKeys WHERE DATEDIFF(second, '1970-01-01', GETDATE()) - timestamp > 72 * 3600`. If you want to use DATEDIFF, use some of date type instead integer. – kba Apr 20 '15 at 11:34
0

I'd just like to note that unix timestamps are not relative to timezone. getdate uses server timezone.

Use getutcdate instead.

DATEDIFF(SECOND,'1970-01-01', getutcdate())

RedBassett
  • 3,469
  • 3
  • 32
  • 56
nemo
  • 1