[AccessD] Database servers and timezones

[Gustav Brock]

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