convert utc to local time from integer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

convert utc to local time from integer

Author

Topic

thussain
Starting Member

7 Posts

Posted - 2005-07-06 : 13:17:58

I am trying to convert these value into local Date time values however not sure what to do. This is a third party vendor database.
The data is appeared as and stored as int data type.
1116012701
1116012865
1116013082
1116019904
1116019951
1116020364
1116021153
1116021212
1116023319

This is the explaination Vendor have for the Timestamp fields

Timestamps
Timestamps within a CDR record appear in universal coordinated time (UTC),
which is the number of seconds since midnight on January 1, 1970. This value
remains independent of daylight saving time changes.

Unsigned 32-bit integers represent all time values. This unsigned integer
value displays from the database as a single integer. The field specifies a
time_t value that is obtained from the Windows NT (2000) system routines.

Thanks
Tanweer

Kristen
Test

22859 Posts

Posted - 2005-07-06 : 13:32:35

DECLARE @MyUTCTime int
SET @MyUTCTime = 1116012701
SELECT [Human Time] = DATEADD(Second, @MyUTCTime, '01-Jan-1970')

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-06 : 13:40:59

don't forget the utc offset to local time


select dateAdd(hour, 
		datediff(hour, getdate(), getutcdate()), --UTC offset
		dateadd(second, 1116012701, '1/1/1970 12:00 AM'))

EDIT:
I guess it should be this:
select dateAdd(hour, 
		datediff(hour, getutcdate(), getdate()), --UTC offset
		dateadd(second, 1116012701, '1/1/1970 12:00 AM'))

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-06 : 19:49:33

You may have to have logic to calculate the offset from UTC at the time of the data you are receiving. In other words, if you are in Eastern time zone and it is July 7, 2005, your UTC offset is -4, but if the data you are receiving was from December 6, 2004, the offset at that time would be -5, so you would not want to use the current UTC offset but the UTC offset that was in effect on December 6, 2004.

Even if the data you are getting is fairly current, you would still get the problem a couple of times per year if you are loading yesterday's data.

quote:

Originally posted by TG

don't forget the utc offset to local time


select dateAdd(hour, 
		datediff(hour, getdate(), getutcdate()), --UTC offset
		dateadd(second, 1116012701, '1/1/1970 12:00 AM'))

EDIT:
I guess it should be this:
select dateAdd(hour, 
		datediff(hour, getutcdate(), getdate()), --UTC offset
		dateadd(second, 1116012701, '1/1/1970 12:00 AM'))

Be One with the Optimizer
TG

CODO ERGO SUM

thussain
Starting Member

7 Posts

Posted - 2005-07-06 : 20:20:07

Great help

And lot of Thanks

Will offset be an issue for different year as the data may be going back many years.

How I am going to know what offset is for what year.

Tanweer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-06 : 22:35:17

The UTC offset changes when Daylight Saving time begins and ends.

Google is your friend. The info in this link may get you started:
http://webexhibits.org/daylightsaving/b.html

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources