what date format is this?

Question

what date format is this?

is250sp

SSCarpal Tunnel

Points: 4361
More actions
June 24, 2008 at 1:03 pm

#77026

example value is 1195507770 which shows in the app as 11/19/2007 03:29 pm
And how do I convert it to datetime?

Viewing 15 posts - 1 through 15 (of 19 total)

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

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

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

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 3

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?

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 4

Do you have a time zone column somewhere?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 7

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?

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 9

I am a visual person. Would you mind showing me examples of how I would incorporate this into the script above?

Sergiy SSC Guru Points: 110208 More actions · Answer 10

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

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?

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 12

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 13

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

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?