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

Question

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.

Answer 1

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

Answer 2

You can convert from SQL Server DateTime to Epoch time by calculating the number of seconds that have elapsed since Jan 1, 1970, like this.

Select DateDiff(Second, '19700101', GetDate())

To get rows from the last 24 hours....

Select Columns
From   Table
Where  EpochColumn Between DateDiff(Second, '19700101', GetDate()) And DateDiff(Second, '19700101, GetDate()-1)