Date difference in Hours format from bigint in SQL Server

Question

I have a column of bigint type containing date & time information (like 1353056515, 1353067040, 1360839600 etc.)

My requirement is to get time difference in HOURS format between column which I mentioned above and current datetime.

I tried to find the solution, but those were so confusing. I'm new to SQL Server.

Please help.

Answer 1

Please try this.

declare @mydate datetime
DECLARE @LocalTimeOffset BIGINT
  ,@AdjustedLocalDatetime BIGINT

SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
SET @AdjustedLocalDatetime = 1416474000 - @LocalTimeOffset

SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime))
-- It will give you date 2014-11-20 14:30:00.000

The data difference operation:

select DATEDIFF(hour,@mydate,GETDATE())

or

Create Function

create  FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
  DECLARE @mydate datetime
  DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT

  SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
  SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
  SELECT @mydate=DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime))
  return @mydate
END;
GO

select mydate= dbo.fn_ConvertToDateTime (1416474000)

select DATEDIFF(hour,@mydate,GETDATE())

Hope that helps.

Answer 2

The number looks like a UNIX timestamp, which is the number of seconds since 1/1/1970 00:00:00 UTC. You can get the UNIX timestamp from a datetime with a simple DATEDIFF:

declare @date datetime='2014-11-20T10:00:00'
declare @epoch datetime='19700101'

select DATEDIFF(s,@epoch,@date)

To get the number of hours between two timestamps you simply need to divide the difference by 3600 (the number of seconds in an hour). You don't need to convert the timestamps to dates before calculating the difference :

declare @dateTS int=DATEDIFF(s,@epoch,@date)
declare @nowTS int=DATEDIFF(s,@epoch,GETUTCDATE())

select (@nowTS-@dateTS)/3600.0

Note that I used 3600.0 to get a decimal result. If 3600 is used the fractional part will be truncated and you'll get 0 for differences less than 1 hour. This is OK if you want to return whole hours.

In a query you could write something like this:

select (DATEDIFF(s,@epoch,GETUTCDATE())-[MY_TS_COLUMN])/3600.0 AS Hours
from [MY_TABLE]