Search code examples
sql-servertimeepoch

How do I compare an epoch time against 24 hours ago in SQL Server?

I am using MS SQL Server 2005, I have dates stored in epoch time (starting 1970) I need to create a statement that will affect any record that has not been updated in the last 24 hours.

Solution

  • To get the current datetime into epoch format, use (via):

    SELECT DATEDIFF(s,'19700101 05:00:00:000',GETUTCDATE())

    To get the epoch time for now - 24 hours use:

    SELECT DATEDIFF(s,'19700101 05:00:00:000', DATEADD(DAY, -1, GETUTCDATE()))

    So, you could do:

    DECLARE @24_hours_ago AS INT
SELECT @24_hours_ago = DATEDIFF(s,'19700101 05:00:00:000', DATEADD(DAY, -1, GETUTCDATE()))

UPDATE table SET col = value WHERE last_updated < @24_hours_ago