June 24, 2008 at 1:03 pm
example value is 1195507770 which shows in the app as 11/19/2007 03:29 pm
And how do I convert it to datetime?
June 24, 2008 at 2:13 pm
It's the number of seconds since 1970-01-01 and can be changed to an SQL Server DATETIME datatype using something like the following...
SELECT DATEADD(ss,1195507770,'1970-01-01')
--Jeff Moden
June 24, 2008 at 2:23 pm
Looks like a POSIX time that is adjusted for a time zone of negative 6 hours (India?) - POSIX or UNIX time is the number of seconds since January 1st 1970 UTC and does not count leap seconds.
selectcast('2007-11-19 15:29:00' as datetime ) as ExpectedTs
,dateadd(ss,1195507770, cast('1970-01-01' as datetime )) - (6.0 / 24 ) as CalculatedTs
SQL = Scarcely Qualifies as a Language
June 24, 2008 at 2:52 pm
Carl Federl (6/24/2008)
Looks like a POSIX time that is adjusted for a time zone of negative 6 hours (India?) - POSIX or UNIX time is the number of seconds since January 1st 1970 UTC and does not count leap seconds.
selectcast('2007-11-19 15:29:00' as datetime ) as ExpectedTs
,dateadd(ss,1195507770, cast('1970-01-01' as datetime )) - (6.0 / 24 ) as CalculatedTs
Thanks that worked, however there is a slight problem. For some values, the correct date is returned by using either 5 or 6 to divide by 24. For example...
value: 1195507770
date in app: 3:29:30
script: dateadd(ss,1195507770, cast('1970-01-01' as datetime )) - (6.0 / 24)
returns: 15:29:30.000 which is correct
if I use....
script: dateadd(ss,1195507770, cast('1970-01-01' as datetime )) - (5.0 / 24)
returns: 16:29:30.000 which is wrong
However, I tested another value...
value: 1214335513
date in app: 2:25:13.000
script: dateadd(ss,1214335513, cast('1970-01-01' as datetime )) - (6.0 / 24)
returns: 13:25:13.000 which is wrong
if I use...
script: dateadd(ss,1214335513, cast('1970-01-01' as datetime )) - (5.0 / 24)
returns: 14:25:13.000 which is correct
How can this be?
June 24, 2008 at 4:22 pm
Do you have a time zone column somewhere?
--Jeff Moden
June 25, 2008 at 8:10 am
Jeff Moden (6/24/2008)
Do you have a time zone column somewhere?
If you mean do we store the time zone in a table then I would have to say no (unless its somewhere in one of the system tables). Btw, I am in the Central time zone.
June 25, 2008 at 5:23 pm
Then what in the application is giving you the offset of either 5 or 6 hours? Could it be that all the times are assumed to be GMT and a 5 or 6 hour offset has automatically been assumed based on whether it's daylight savings time or not?
--Jeff Moden
June 26, 2008 at 8:09 am
Jeff Moden (6/25/2008)
Then what in the application is giving you the offset of either 5 or 6 hours? Could it be that all the times are assumed to be GMT and a 5 or 6 hour offset has automatically been assumed based on whether it's daylight savings time or not?
I dont know...thats what I am trying to figure out. It obvious the app is doing the conversion to display the correct time, but the question is how is it doing it, or better yet, what logic is it using?
June 26, 2008 at 8:19 am
I think all the times are GMT and it's doing just like I said... it's dipping the operating system to find what the local time offset from GMT is and the recalculating the time based on that... we can do the same thing in SQL...
SELECT GETDATE()-GETUTCDATE() AS OffSet
Ignore the fact that that displays a 1899 date... the real fact is that it's a certain number of hours less than "0" time... if you add it to the date time, you will come up with the correct local time although you may have to do a jiggle to compensate for daylight savings time.
--Jeff Moden
June 26, 2008 at 8:50 am
I am a visual person. Would you mind showing me examples of how I would incorporate this into the script above?
June 26, 2008 at 3:02 pm
Jeff,
look again on the second post from OP.
He wants DIFFERENT OFFSETS for different days.
No matter how hard you try with GetUTCDate or registry reading it will always give you the same time offset for different dates in SELECT.
is250sp,
take some time and study your application.
There is time zone stored somewhere in line with the dates.
_____________
Code for TallyGenerator
June 26, 2008 at 3:11 pm
Could daylight savins time be the differentiating factor? This would explain the discrepancy with no timezone factor involved.....
After all - the "-5" on is in the summer (GMT-5), and the winter one is "-6"......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 26, 2008 at 4:52 pm
is250sp (6/26/2008)
I am a visual person. Would you mind showing me examples of how I would incorporate this into the script above?
Ummm... sure... here's the original script that Carl made...
select cast('2007-11-19 15:29:00' as datetime ) as ExpectedTs
, dateadd(ss,1195507770, cast('1970-01-01' as datetime )) - (6.0 / 24 ) as CalculatedTs
... you would make a simple substitution... the - (6.0 / 24 ) would be replaced by the forumula I gave...
select cast('2007-11-19 15:29:00' as datetime ) as ExpectedTs
, dateadd(ss,1195507770, cast('1970-01-01' as datetime )) + (GETDATE()-GETUTCDATE() ) as CalculatedTs
Like I said and like Segiy just said... there is another factor that you have to in here... you have to add in when daylight savings time occurs and subtract (I think) an hour when DST kicks in not only for GETDATE(), but for when the date itself occurs.
--Jeff Moden
June 26, 2008 at 4:56 pm
Matt Miller (6/26/2008)
Could daylight savins time be the differentiating factor? This would explain the discrepancy with no timezone factor involved.....After all - the "-5" on is in the summer (GMT-5), and the winter one is "-6"......
Already there... see the previous post where I say...
although you may have to do a jiggle to compensate for daylight savings time
--Jeff Moden
June 26, 2008 at 5:18 pm
Jeff Moden (6/26/2008)
Matt Miller (6/26/2008)
Could daylight savins time be the differentiating factor? This would explain the discrepancy with no timezone factor involved.....After all - the "-5" on is in the summer (GMT-5), and the winter one is "-6"......
Already there... see the previous post where I say...
although you may have to do a jiggle to compensate for daylight savings time
I was wondering why that hadn't been brought up...:) I've been off coffee for the last few days, so eagle-eyed I am not today....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply