question

Drahc avatar image
Drahc asked

Convert SQL string to Epoch time?

Hello. I need to convert this input string 11/20/2015 7:00 PM GMT +0:00 to an Epoch time format and unsure how to do this? When I try something like SELECT DATEDIFF(s, '1970-01-01 00:00:00', '11/20/2015 7:00 PM GMT +0:00') it does not work and I assume that is because 11/20/2015 7:00 PM GMT +0:00 is not in the proper format for the statement. If I use the epochconverter.com Human Date to Timestamp converter it works so I know it is possible. I assume some other functions like Java script etc are at play though. I need strictly SQL conversions though.
converttimestamp
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fantastic. That works! Input/Output/Result in my time zone ET. '11/20/2015 7:00 PM GMT -5:00' 1448064000 Friday, November 20, 2015 7:00:00 PM GMT-5:00 '11/20/2015 7:00 PM GMT +0:00' 1448046000 Friday, November 20, 2015 2:00:00 PM GMT-5:00 '11/20/2015 7:00 PM GMT +10:00' 1448010000 Friday, November 20, 2015 4:00:00 AM GMT-5:00
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
First, I'll admit I had to look up Epoch Time via the link you provided. For anyone else who doesn't know ... The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'. Many Unix systems store epoch dates as a signed 32-bit integer, which might cause problems on January 19, 2038 (known as the Year 2038 problem or Y2038). They actually do provide a SQL Server solution on that site: SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) The GETUTCDATE() function returns the current UTC date/time on the server. To use a different date (or string), we first want to ensure we have a datetime2 value. If all of your date/time values are GMT expressed as that example, the following should work: DECLARE @startdate datetimeoffset(0) = CONVERT(datetimeoffset(0), '19700101', 112); DECLARE @datestring varchar(50) = '11/20/2015 7:00 PM GMT +0:00'; DECLARE @datevalue datetimeoffset(0) = CONVERT(datetimeoffset(0), REPLACE(@datestring, ' GMT', '')); SELECT DATEDIFF(second, @startdate, @datevalue); --------------- UPDATE: The short answer to your question about an end date 4 hours later is to just add (4*3600) to the start time because Epoch time is a count of seconds. That said, I decided to create a table-valued function to return the start and end Epoch times given a specific time and the number of hours until the end. I'll show you the function and then give an example of how to use it. CREATE FUNCTION dbo.GetEpochStartAndEnd ( @StartDateString varchar(50) , @HoursUntilEnd int ) RETURNS TABLE AS RETURN ( SELECT StartEpochTime = DATEDIFF ( second , CONVERT(datetimeoffset(0), '19700101', 112) , CONVERT(datetimeoffset(0), REPLACE(@StartDateString, ' GMT', '')) ) , EndEpochTime = DATEDIFF ( second , CONVERT(datetimeoffset(0), '19700101', 112) , CONVERT(datetimeoffset(0), REPLACE(@StartDateString, ' GMT', '')) ) + @HoursUntilEnd * 3600 ); Here is an example of how you could use this: SELECT o.modify_date, e.StartEpochTime, e.EndEpochTime FROM sys.objects o CROSS APPLY dbo.GetEpochStartAndEnd(CONVERT(varchar(50), o.modify_date, 121), 4) e ;
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That appears to return the result of 1448046000 I am looking for. I am going to code this in with my variables and see what happen! Thanks
0 Likes 0 ·
Ah so here is the fun part. I also need to be able to convert GMT -5:00 and GMT +10:00 which the DATEDIFF seems to ignore. If I put in '11/20/2015 7:00 PM GMT -5:00' or '11/20/2015 7:00 PM GMT +10:00' I get the same result as '11/20/2015 7:00 PM GMT +0:00' Thanks
0 Likes 0 ·
I updated my answer. Sorry I didn't think of this before, but all I had to do was remove the " GMT" part of your date string and then use the datetimeoffset data type.
0 Likes 0 ·
Last part to all this, I need a second date, call it end date, that is exactly 4 hrs after the date output of this statement, but also needs to be in Epoch/Unix time when passed. I am new to SQL and inherited this activity with some lovely form processing and db work thats need updating!
0 Likes 0 ·
Thank you!
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
Not sure what happened, but it's back to saying there are no answers. I'm just adding this one to get it to at least report one.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Did you convert an answer by the OP into a comment? That'll throw the numbering off...
1 Like 1 ·
Yeah. I thought that may have been the case, but it's still annoying. :)
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.