Converting UTC stored datetime into local time

Converting UTC stored datetime into local time

Post by Marin » Wed, 11 Aug 2004 04:27:09


Hi,

Let's say a query tries to convert UTC time into local time. The program
can figure out the number of hours between UTC and local time, and construct
the query appropriately.

However, that is only valid for some dates - since due to daylights savings
time, some dates will be an extra hour off.

Is there a way to get SQL Server, to convert a given UTC date to local time,
without explicitly adding/subtracting a set # of hours (since that doesn't
work for all dates)?

Thanks
 
 
 

Converting UTC stored datetime into local time

Post by Glen » Wed, 11 Aug 2004 12:33:37

Hi Marina,

You can use the GETUTCDATE() function to return the UTC datetime and
subtract the GETDATE() function to calculate the number of hours to
adjust the value. For example:

select MyUTCDateColumn + (GETUTCDATE() - GETDATE()) as MyLocalDate
from MyTable

Using this method, the value will be correct based on the server
settings of time zone and daylight savings.

Hope this helps,
- Glen

 
 
 

Converting UTC stored datetime into local time

Post by Steve Kas » Wed, 11 Aug 2004 15:59:46

len,

Your expression will convert the stored UTC time as if it were stored
in the same time zone as the current one. This will give the wrong
answer if the time was stored during Daylight Savings Time and it is now
Standard Time or vice versa.

One solution to this problem is to maintain a table with the
UTC-to-Local offsets and effective dates and convert each UTC time based
on that table, using the latest effective date before or on the time
being converted:

create table TimeChanges (
effectiveUTC datetime primary key,
OffsetHours int,
LocalTimeZone char(3),
OffsetDT as cast(OffsetHours as float)/24
)

insert into TimeChanges values ('06-Apr-2003 07:00',-4,'EDT')
insert into TimeChanges values ('26-Oct-2003 06:00',-5,'EST')
insert into TimeChanges values ('04-Apr-2004 07:00',-4,'EDT')
insert into TimeChanges values ('31-Oct-2004 06:00',-5,'EST')
insert into TimeChanges values ('03-Apr-2005 07:00',-4,'EDT')
go

create table TestData (
UTC datetime primary key
)
insert into TestData values ('26-Oct-2003 05:59')
insert into TestData values ('26-Oct-2003 06:00')
insert into TestData values ('26-Oct-2003 06:01')
insert into TestData values ('04-Apr-2004 06:59')
insert into TestData values ('04-Apr-2004 07:00')
insert into TestData values ('04-Apr-2004 07:59')
insert into TestData values ('08-Jul-2004 17:30')

select UTC, UTC + offsetDT as LocalTime, LocalTimeZone
from TestData join TimeChanges
on effectiveUTC = (
select max(effectiveUTC)
from TimeChanges
where effectiveUTC <= UTC
)
go
drop table TimeChanges, TestData


Another approach that might be more efficient, but which is not as easy
to read, is this:

create table TimeChanges (
effectiveUTC datetime primary key,
OffsetHours int,
OffsetDT as cast(OffsetHours as float)/24
)

insert into TimeChanges values ('06-Apr-2003 07:00',-4)
insert into TimeChanges values ('26-Oct-2003 06:00',-1)
insert into TimeChanges values ('04-Apr-2004 07:00',+1)
insert into TimeChanges values ('31-Oct-2004 06:00',-1)
insert into TimeChanges values ('03-Apr-2005 07:00',+1)
go

-- create table TestData as before

select UTC, UTC + sum(offsetDT) as LocalTime
from TestData join TimeChanges
on effectiveUTC <= UTC
group by UTC
go

--drop table TimeChanges, TestData

Here's yet another that's better in terms of table normalization, and
which might be useful if your data comes from more than one timezone.

create table TimeChanges (
effectiveUTC datetime primary key,
LocalTimeZone char(3)
)

insert into TimeChanges values ('06-Apr-2003 07:00','EDT')
insert into TimeChanges values ('26-Oct-2003 06:00','EST')
insert into TimeChanges values ('04-Apr-2004 07:00','EDT')
insert into TimeChanges values ('31-Oct-2004 06:00','EST')
insert into TimeChanges values ('03-Apr-2005 07:00','EDT')
go

create table TimeZoneOffsets (
TimeZone char(3) primary key,
OffsetHours int,
OffsetDT as cast(OffsetHours as float)/24
)
insert into TimeZoneOffsets values ('EST', -5)
insert into TimeZoneOffsets values ('EDT', -4)
GO

-- create table TestData as before

select
T2.UTC, T2.UTC + T1.OffsetDT as LocalTime, T1.TimeZone
from TimeZoneOffsets T1 join (
select
UTC,
( select top 1 LocalTimeZone
from TimeChanges
where effectiveUTC <= UTC
order by effectiveUTC desc
) as LocalTimeZone
from TestData
) T2
on T1.TimeZone = T2.LocalTimeZone
go

 
 
 

Converting UTC stored datetime into local time

Post by Marin » Wed, 11 Aug 2004 21:50:28

ounds like there is no easy way to do this. Very unfortunate that there is
no built in function to convert a given UTC date into local time and vice
versa.

Thanks

"Steve Kass" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...