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

[Stuart McLachlan]

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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>