[AccessD] [dba-SQLServer] Database servers and timezones

[Gustav Brock]

Hi Stuart

I had forgot about GetUtcDate() so I added this to the query and it returned the same value as GetDate().
So Azure SQL runs at UTC. Of course, you can say - what other option could be universally useful.

I tried this:

    CAST(GetDate() AS datetimeoffset(3)) AS  'datetimeoffset test' 

It returns a string: 2016-04-24 10:42:02.467 +00:00
so it just shows the offset on the server. What I am thinking of, is how to handle the offset between users and/or the server in different timezones.

/gustav

________________________________________
Fra: dba-SQLServer <dba-sqlserver-bounces at databaseadvisors.com> på vegne af Stuart McLachlan <stuart at lexacorp.com.pg>
Sendt: 24. april 2016 11:42
Til: Access Developers discussion and problem solving; Discussion concerning MS SQL Server      ddba-sqlserver at databaseadvisors.com"; Access Developers discussion andpproblem solving
Emne: Re: [dba-SQLServer] [AccessD] Database servers and timezones

If you are storing current times, then use getutcdate() instead of getdate().

When you are working in locations with DST with historical or future dates, it gets much more
difficult.

Also take a look at datetimeoffset.

--
Stuart


On 24 Apr 2016 at 7:43, Gustav Brock wrote:

> 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