Re: Dates & international representation

Joe Celko wrote:
> >> Is there a general approach to translating times in a SQL database
> (MS SQL Server in my case) to local time from UTC? At my last job, we
> had a table for translating cities to offsets and a table for daylight
> savings, but that led to joins and calculations that I hope are
> unnecessary. <<
>
> You got it! The SQL-92 Standard has such a table in the Schema
> Information tables. All SQL is supposed to be in UTC and translated
> toocal time.
>
> This schema information table holds the time zone and daylight saving
> time (i.e. "Lawful time") and the system is supposed to convert it. We
> also use the leap second in the Standard.
>
> The general problem for the world is a mess. We had lawful time zones
> which were not a even number of hours off of UTC until two years ago.

Newfoundland and India (to name but two) use time zones that are an odd number of half-hours off UTC. And IIRC Bhutan or Nepal uses a time zone a quarter of an hour off Indian time, just to emphasize that they aren't India (well, that's my outsider's view of the subject -- they probably have a different view on why it is as it is).

-- 
Yours,
Jonathan Leffler (Jonathan.Leffler_at_Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
     "I don't suffer from insanity; I enjoy every minute of it!"