[AccessD] Database servers and timezones

[Gustav Brock]

Hi Jim

OK, that makes sense. I realize that locality can be many things. My thought was the timezone of such a locality, but I guess that if you have coordinates or just, say, the city, you can always figure out the timezone. That leaves the issue with DST so if that has any importance, I believe you still will have to save the local time or you will face endless conversion functions keeping track of dates to know if DST is active or not at a given date.

/gustav

________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af James Button <jamesbutton at blueyonder.co.uk>
Sendt: 24. april 2016 18:25
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Database servers and timezones

Basic tenet in most of the systems I have worked with/on:
Database activity - entry login etc .- GMT
Anything that is to be related to a locality - add the timezone ( and summertime
- daylight saving ) to get time at the related location -
So - database activity timestamps are in correct sequence and things like the
time that something should happen is local and would have a locality, and yes -
you would need to record something that indicates the relevant locality  (have
fun with that) - deliveries, servicing and meetings, then you can convert the
local happening to head--office (corporate) time.

JimB

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Sunday, April 24, 2016 12:25 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Database servers and timezones

Hi Jim

Yeah, I can see the trouble extracting dates only from times that have been
converted to UTC or just another timezone.

So, when specifying a pickup time, you write the local time at the pickup
location as the timezone in such cases really has no importance.

Perhaps my thoughts were more at accounting. But here normally only the date
matters - if an order is entered at 08:00 or 14:00 may not be important.
Perhaps you should record such times as the "date at noon" (midday)? Or just
regard a "clean" date with no time part as "this date at noon"? Then, for all
practical purposes I can think of, you can convert between local time and UTC
without loosing the date.

But it very much depends, of course.

I'm happy you can use some of my date stuff. Actually, I'm about collecting it
as time allows, but it really is a major piece of work (16 code modules until
now) cleaning up and commenting it - not to say testing and improving, as my own
requirement is that all functions must handle the entire range of Date with an
accuracy of 1 millisecond. Also, I wonder where to publish it when time comes.
Neither EE nor CodeProject seems to be the perfect place.

/gustav

________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Jim Dettman
<jimdettman at verizon.net>
Sendt: 24. april 2016 12:06
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Database servers and timezones

 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