UTC converted to local time, current and historical?

UTC converted to local time, current and historical?

Post by Michael Ma » Thu, 04 Oct 2007 04:21:46


We store all of our dates as UTC however we would like to retrieve these
according to the local time zone setting for both current and historical
dates and times. There doesn't seem to be a function that performs the
reverse of GETUTCDATE(), i.e. it gets the local date & time based on the
current time zone setting of the user. Does anyone know of an easy way to do
this?

We have considered using CLR but to be honest we don't want to do that for
reasons that don't really need to be mentioned here.

TIA

Michael MacGregor
Database Architect
 
 
 

UTC converted to local time, current and historical?

Post by Anith Se » Thu, 04 Oct 2007 07:18:27

>> There doesn't seem to be a function that performs the reverse of

Are you referring to the client side setting or that of the server? Can you
post an example that illustrates this ?

--
Anith

 
 
 

UTC converted to local time, current and historical?

Post by Erland Som » Thu, 04 Oct 2007 07:35:37

Michael MacGregor ( XXXX@XXXXX.COM ) writes:

I'm not sure that I understand completely, and it's late over here, so I'm
probably completely wrong, but it sounds like you are asking for:

SELECT dateadd(MINUTE,
DATEDIFF(MINUTE, getutcdate(), getdate()), timecol)

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

UTC converted to local time, current and historical?

Post by Mark J. Mc » Thu, 04 Oct 2007 07:39:37

"Michael MacGregor" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

In a nutshell, no, there is no simple way. The task is basically applying
local time zone bias, determining whether or not daylight savings time
applies (based on locale, time of year, and in some cases year) and then
applying additional DST bias as appropriate for the client locale.

To do this you will need, for each client system's local time zone:

standard time zone bias
daylight savings time zone bias
daylight savings start date/time
daylight savings end date/time

Realistically, both of the bias values must be obtained from the individual
clients' systems, that's the only unambiguous way to support those odd-ball
pockets that don't observe DST, like Arizona, for example.

DST start and end dates can be inferred from standard bias, for the USA and
for the EU -- but note that the rules are different: in the USA, DST
starts/ends at 2AM local time; in the EU, it starts/ends at 1AM UTC.

Also, to really be correct for USA dates, conversions would need to observe
the difference in rules for dates prior to 2007, and dates on or after
2007... but the reality is that perfectly correct converted dates will
differ from MS Office converted dates on all O/S prior to Vista, and I
suspect that even there, a great many apps will still get it wrong, due in
no small part, to the insanity with which every Windows API that performs
such conversions it implemented...

Examples? Absolutely!

[tech ref]
(Docs for SystemTimeToTzSpecifcLocalTime) SystemTimeToTzSpecifcLocalTime
{insanely} uses the current settings for the time zone and daylight saving
time. Therefore, if it is daylight saving time, this function will
{insanely} take daylight saving time into account, even if the time you are
converting is in standard time.

(Article ID: Q128126 )

Under NTFS, the API GetFileTime() returns the create time, last access time,
and last write time for the specified file. The times returned in the
FILETIME structures are in Universal Coordinated Time (UTC). This is also
the time that NTFS uses. You can use FileTimeToLocalFileTime() to convert a
file time to a local time. However, if you automatically adjust for Daylight
Saving Time, FileTimeToLocalFileTime() will {insanely} adjust for Daylight
Saving Time based on whether the current date should be adjusted for
Daylight Saving Time, not based on whether the date represented by the
FILETIME structure should be adjusted.

(Docs for FileTimeToLocalFileTime)

FileTimeToLocalFileTime {insanely} uses the current settings for the time
zone and daylight saving time. Therefore, if it is daylight saving time,
this function will {insanely} take daylight saving time into account, even
if the time you are converting is in standard time.

[/tech ref]

Anyways, before I completely wander from the topic, I've written a UDF to do
the conversion, for USA time zones only, and with 2006/2007 logic
dumbed-down/commented-out so my conversions will match Office conversions on
XP systems and earlier. Its algorithm for deriving DST start/end is said to
be valid through 2099.

Adding support for EU time zones would be trivial, the main differences
being how the DST start and end dates are derived, and how to determine
whether or not DST applies. To support the entire world, and/or dates after
2099, you'd need to tablize the
 
 
 

UTC converted to local time, current and historical?

Post by Razvan Soc » Fri, 05 Oct 2007 15:00:02


Erland,

I think Michael was refferring to the local time zone of the client, not of the server.

Anyway, the "DATEDIFF(MINUTE, getutcdate(), getdate())" method may give incorrect results (very
rare, but possible). I tried running the following code (I am in the GMT+3:00 timezone, if you are
in a different time zone, replace 180 with something else):

DECLARE @N1 INT, @N2 INT, @N3 INT, @N4 INT, @N5 INT, @N6 INT, @N7 INT, @N8 INT
SELECT @N1=0, @N2=0, @N3=0, @N4=0, @N5=0, @N6=0, @N7=0, @N8=0
WHILE 1=1 BEGIN
DECLARE @A DATETIME, @B DATETIME
SELECT @A=GETDATE(), @B=GETUTCDATE()
SET @N1=@N1+1
IF DATEDIFF(MINUTE,@B,@A)<>180 SET @N2=@N2+1
IF DATEDIFF(SECOND,@B,@A)<>180*60 SET @N3=@N3+1
IF DATEDIFF(MILLISECOND,@B,@A)<>180*60*1000 SET @N4=@N4+1
IF ROUND(DATEDIFF(SECOND,@B,@A),-1)<>180*60 SET @N5=@N5+1
IF ROUND(DATEDIFF(MILLISECOND,@B,@A),-1)<>180*60*1000 SET @N6=@N6+1
IF ROUND(DATEDIFF(MILLISECOND,@B,@A),-2)<>180*60*1000 SET @N7=@N7+1
IF ROUND(DATEDIFF(MILLISECOND,@B,@A),-3)<>180*60*1000 SET @N8=@N8+1
IF @N1%1000000=0 SELECT @N1 N1, @N2 N2, @N3 N3, @N4 N4, @N5 N5, @N6 N6, @N7 N7, @N8 N8
END

After a long time (on a Vista computer with SQL 2005), here is what I got:

N1 N2 N3 N4 N5 N6 N7 N8
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
100000000 1 2824 779692 0 209806 35 0

However, on a WinXP computer with SQL 2000, I got fewer differences:

N1 N2 N3 N4 N5 N6 N7 N8
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
379000000 0 20 1464 0 1464 0 0

Therefore, instead of using "DATEDIFF(MINUTE, getutcdate(), getdate())", I would use
"ROUND(DATEDIFF(Second, GETUTCDATE(), GETDATE()),-1)/60".

--
Razvan Socol
SQL Server MVP
 
 
 

UTC converted to local time, current and historical?

Post by Michael Ma » Sat, 06 Oct 2007 00:05:37

Hi Mark,

You hit the nail on the head.

We have customers across North America in different time zones but all dates
are saved in a central database in UTC format, however customers like to see
their dates according to their local time zone settings, and adjusted for
daylight savings.

By interrogating the registry it would be possible to do this using CLR but
our developers would prefer not to use that approach so if you have a UDF I
would be very interested in seeing it. Actually I just learnt something else
as well, there are some unusual kinks in the logic with states such as Idaho
that has two time zones, Pacific and Mountain, and Indiana where half of the
state observes daylight savings but the other half doesn't (anybody watch
West Wing?), and where Ontario also sits in two time zones. Plus we have a
central call centre location that processes data on behalf of our customers
and interrogating the registry simply wouldn't work in that instance as it
would pick up our local time zone instead of the customers.

At this time we are only concerned with North America, which makes it at
least a little simpler, though in the long term we may need to account for
other countries/continents.

Anyway, I would be very interested in seeing your UDF.

Thanks,

Michael MacGregor
Database Architect
 
 
 

UTC converted to local time, current and historical?

Post by Tom Coope » Sat, 06 Oct 2007 01:02:30

It's going to be very difficult, and particularly difficult if you are
interested in historical datetimes. That's because the rules for who
observes daylight savings time (DST) and who doesn't and the dates DST
starts and stops have changed many times (most recently this year).

For example, it is no longer true that some parts of Indiana observe DST and
some don't. It used to be true that some counties in Indiana were in the
eastern time zone, and went on DST, some were in the eastern time zone, but
did not go on DST, and some were in the central time zone (all of those went
on DST). But, as of April 2, 2006 all of Indiana goes on DST. At the same
time, however, eight counties were moved from the eastern time zone to the
central time zone. On March 11, 2007, one of those couties moved back to
the eastern time zone.

And it's not just one or two states that have part of the state in one time
zone and part in another. There are at least 13 (OR, MT, AZ, ND, SD, NE,
KS, TX, MI, IN, KY, TN, and FL).

And I don't think a CLR will help. I don't use CLR's, but I'm pretty sure
any CLR is going to run on the server, so when it checks the registry, it
will be checking the registry of the server, not the client.

Tom
 
 
 

UTC converted to local time, current and historical?

Post by Michael Ma » Sat, 06 Oct 2007 01:46:09

Hi Tom,

Thanks for the update on the different time zones for the various states,
especially Indiana, though really that isn't much of a concern for us right
now as we don't have that state as a customer, yet.

Yes it would have to be historical, and after some discussion with the lead
architect here, and using some of the information gleaned from this thread
so far, it would seem that we would have to bring the necessary data into
the database, time zones, DST date and times, etc. Fortunately our customers
have fixed locations so we can determine the specific time zone and DST
information for each, and on an historical basis.

The UDF would then use the data stored in the database to determine what the
datetime setting should actually be, whether accessed by a client or central
location.

You are correct in that a CLR would not be of much use but only for
information processed at our central location as any datetime dependant
operation has to be performed according to the customers time zone setting
not ours. However for customer locations it is not a concern as they have
SQL installed locally and so the CLR would operate correctly in picking up
the local time zone settings. Sorry I didn't specify that the remote
locations all have SQL installed.

Thanks,

Michael MacGregor
 
 
 

UTC converted to local time, current and historical?

Post by Erland Som » Sat, 06 Oct 2007 07:34:05

Razvan Socol ( XXXX@XXXXX.COM ) writes:

That will be difficult, given that the server has no knowledge of the
client's time zone. Possibly the application could set it with
SET CONTEXT_INFO. But I think it would be better to have this in
the client.


Because getdate and getutcdate get executed separately, and could end
up in different minutes?

It was late when I made my previous post, and it's late when I making this
post, so I didn't come around to run your repro.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

UTC converted to local time, current and historical?

Post by Michael Ma » Sat, 06 Oct 2007 23:01:17

In the part of this thread with Tom Cooper, I provided some more details
about the system architecture, the objectives and some of the issues
involved.

Our client locations have SQL running there as well, with data being
synchronised with the central location. Client locations only contain
information for that location. All locations are stored in the central
database. So it is possible to determine the time zone settings of the
client machine, however that doesn't help much when we are processing client
data in the central location where the local time zones for the client must
also be used. In this case no it is not possible to determine the local time
zone by interrogating machine settings.

I have to admit that I am a little surprised that there are not solutions
for this particular problem, I would have expected that this might have been
more common. Oh well, guess not.

Michael MacGregor
Database Architect
 
 
 

UTC converted to local time, current and historical?

Post by Mark J. Mc » Sun, 07 Oct 2007 00:33:28

I'm assuming your app is browser based? If so you can obtain the bias
values using JavaScript as such:

d = new Date(1, 1, 2007);
stdbias = d.getTimezoneOffset();
d = new Date(6, 1, 2007);
dstbias = d.getTimezoneOffset();


-- @direction = -1 : utc to local
-- @direction = 1 : local to utc
returns datetime

AS
begin

declare @tmpdate datetime
declare @retdate datetime

declare @year int
declare @stdmonth varchar(2)
declare @stdday int
declare @dstmonth varchar(2)
declare @dstday int

declare @dstdate datetime
declare @stddate datetime


set @year = YEAR(@date)

/*
if @year > 2006
begin
*/
Set @stdmonth = '11'
Set @dstmonth = '3'
Set @dstday = 14 - (convert(int, 1 + @year * 5 / 4) % 7)
Set @stdday = 7 - (convert(int, 1 + @year * 5 / 4) % 7)
/*
end
else
begin

Set @stdmonth = '10'
Set @dstmonth = '4'
Set @dstday = (2 + 6 * @year - convert(int, @year / 4)) % 7 + 1
Set @stdday = (31 - (convert(int, @year * 5 / 4) + 1) % 7)

end
*/

set @dstdate = convert(varchar(4), @year) + '-' + @dstmonth + '-' +
convert(varchar(2), @dstday) + ' 2:00 AM'
set @stddate = convert(varchar(4), @year) + '-' + @stdmonth + '-' +
convert(varchar(2), @stdday) + ' 2:00 AM'

if @direction = -1
set @tmpdate = dateadd(mi, @stdbias, @date)
else
set @tmpdate = @date

if (@tmpdate > @dstdate) and (@tmpdate < @stddate)
set @retdate = dateadd(mi, @dstbias * @direction, @date)
else
set @retdate = dateadd(mi, @stdbias * @direction, @date)


return @retdate
end


-Mark
 
 
 

UTC converted to local time, current and historical?

Post by Mark J. Mc » Sun, 07 Oct 2007 00:34:12

ATEDIFF(MINUTE, getutcdate(), getdate()) is plenty accurate, time zone bias
is expressed in minutes. All that your loop does is explore the accuracy
limitations of SQL Server's internal datetime storage.

-Mark


"Razvan Socol" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

UTC converted to local time, current and historical?

Post by Mark J. Mc » Sun, 07 Oct 2007 02:34:19

ops, that should be:

d = new Date(2007, 1, 1);
stdbias = d.getTimezoneOffset();
d = new Date(2007, 6, 1);
dstbias = d.getTimezoneOffset();

-MM


"Mark J. McGinty" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...