Convert DateTime to Epoch

Question

Post reply

Convert DateTime to Epoch

brickpack

SSCrazy

Points: 2702
More actions
April 7, 2009 at 6:30 pm

#130540

Hello,
I'm trying to create a function that will convert a datetime
'2009-04-01 00:00:00.000' to epoch. The catch is I need to account for
my timezone. The following function does the conversion to GMT. I need
it to convert to MST. Please help.
CREATE function [dbo].[date2timestamp]
(@dateStr char(255))
returns bigint
as
begin
return convert(bigint, datediff(ss, '01-01-1970 00:00:00',@dateStr))
end
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

just use a combination of two built in CONVERT formats:

SELECT CONVERT(VARCHAR,@date,112) + ' ' + CONVERT(VARCHAR,@date,114)

--results: 20090407 20:58:00:280

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 2

duh...format 121 does exactly what you need, sorry i misread:

FormattedDate Code SQL

2009-04-07 20:58:00.280 121 SELECT CONVERT(VARCHAR,@date,121)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

brickpack SSCrazy Points: 2702 More actions · Answer 3

Lowell, Thanks for the reply.

Let me clarify what I'm trying to do. I have a column that has a Timestamp (epoch unix timestamp) datatype. I need to create a function or two that will give me the ability to convert the epoch timestamp into DateTime ('2009-04-01 00:00:00.000') and DateTime into epoch. And I need to account for Mountain Standard Timezone. Does that make Sense?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Lowell (4/7/2009)
duh...format 121 does exactly what you need, sorry i misread:
FormattedDate Code SQL
2009-04-07 20:58:00.280 121 SELECT CONVERT(VARCHAR,@date,121)

Heh, format 121 is my favorite by far, I hardly ever use anything else as I find the I can extract most other formats that I want as simple substrings out of it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

dave b (4/7/2009)
Lowell, Thanks for the reply.
Let me clarify what I'm trying to do. I have a column that has a Timestamp (epoch unix timestamp) datatype. I need to create a function or two that will give me the ability to convert the epoch timestamp into DateTime ('2009-04-01 00:00:00.000') and DateTime into epoch. And I need to account for Mountain Standard Timezone. Does that make Sense?

I assume that you mean to the internal storage format of the Linux epoch format? Unfortunately, I do not know what that is, and I am not finding it on Goggle... ?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lowell SSC Guru Points: 323504 More actions · Answer 6

ok my google-fu is strong tonight;

SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) --<<SQL Date to Epoch Date

SELECT DATEADD(s, 1239156328, '1970-01-01 00:00:00') --<<Epoch Date to SQL Date

found that over at http://www.epochconverter.com/[/url]

im not sure if you need to add any adjustments for Mountain time, though.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

brickpack SSCrazy Points: 2702 More actions · Answer 7

Yup...I stumbled on that site today in my search for a solution. That's where I got the idea for the original function.

Good news. I figured it out. Here's what I came up with. This example uses the function to do the conversion and then gets the date range for the previous month +7 hours.

CREATE function [dbo].[date2timestamp]

(@dateStr varchar(50))

RETURNS bigint

AS

BEGIN

RETURN convert(bigint, datediff(ss,'01-01-1970 00:00:00',@dateStr))

END

DECLARE @FirstDay varchar(50), @LastDay varchar(50)

SET @FirstDay = (SELECT DATEADD(hh,7,DATEADD(mm,DATEDIFF(mm,0,getdate())-1,0)))

SET @LastDay = (SELECT DATEADD(hh,6,DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE()),0))))

The data in the following 'startdate' column is stored as the UNIX Epoch time +7 hours (eg...1235890800) as previously mentioned.

SELECT sum(duration) FROM table

WHERE startdate >= dbo.date2timestamp(@FirstDay) AND startdate < dbo.date2timestamp(@LastDay)

Instead of adjusting the function...I adjusted the input date.

Thanks for your replies

forsqlserver SSCoach Points: 18902 More actions · Answer 8

forsqlserver

SSCoach

Points: 18902

December 14, 2012 at 1:37 am

#1568585

Looping.I have the same prob.

Thanks