[AccessD] Database servers and timezones

[Jim Dettman]

 You let a server run with the time zone set, which always has an offset
back to UTC.  For DST situations, the offset is changed 2x a year.

 In an application, you either:

1. Don't do anything except store the time.
2. Store the date/time with a UTC offset and convert as needed. 
3. Store UTC.

  Really depends on the app and even using UTC, which you would think would
be the best method, can really mess people up

  I just did one recently where I was feeding pickup date/times to logistics
company in a request for routing.   Took the local D/T in California and
converted to UTC, which backed up the date.  The UTC messed them up and we
had trucks showing up a day early despite the fact that I gave "UT" as the
qualifier.  Ended up doing the date/times as local date/time in the request.


 And by the way, let me take this opportunity to say "Thank you" for your
date/time routines.   I've used more than a few in my apps<g> (notice
included of course).   

 I can even say I have a "gustav collection" now...really good stuff.

Jim.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Gustav Brock
Sent: Sunday, April 24, 2016 03:44 AM
To: Discussion concerning MS SQL Server
(dba-sqlserver at databaseadvisors.com); Access Developers discussion and
problem solving
Subject: [AccessD] Database servers and timezones

Hi all

When I think about it, I have seen close to nothing on how to handle
timezones in database servers when users are spread across several
timezones. Given that alone the US spans a handful of timezones and Russia
elleven(!), this must be a common task to address.

I just ran a test from one of our Azure SQL databases:

    select getdate() from sometable

and it returned 

    2016-04-24 07:03:02.187 

which equaled the current UTC time.

This server is located in "Northern Europe", actually Ireland I think, so
that is not a big surprise, but which timezone do you in the US set on a
server located, say, in Chicago, that also will service users in Alaska or
Hawaii? And how do you convert to and from the timezones, not to say deal
with DST?

Perhaps one should store both the UTC time, the local (or "source") time,
and the timezone difference (including DST offset) as - in queries - it will
be very slow always to have to convert time either to or from UTC.

I have never dealt with applications expected to operate across timezones,
and servers have been local only, so I have safely could use Date() (and
GetDate() in PT queries) but this picture changes if I move data to the
cloud.

/gustav
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com