On 21/11/2017 14:16, Lester Caine wrote: > On 21/11/17 14:55, Adriano dos Santos Fernandes wrote: >> Implementing TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE datatypes >> as specified in the SQL standard is not a big problem. > The only question is just how is the timezone data managed. It is > useless simply having an offset value when large areas of the planet > still use daylight saving. If I move a meeting from March to April I > need to know the real timezone, a simple hour offset is no good at all. > It's the same old problem as the offset provided by a browser ... > useless for half of the year ... > I must agree with Lester. Initially I want to only implement offsets, but testing Oracle, PostgreSQL and reading the standard, I think offsets are useless for almost everything.
The standard (2013) does not even talk about DST, so it's very useless to manage time zones. A good example for TIMESTAMP WITH TIME ZONE is a calendar application. If I want to register a appointment in my same time zone as I'm but five months later, I do not want to even think that now is in DST time and then will be not. If my appointment will be in New York, it's the same. I do not need to mentally convert my time zone. The meeting in New York is in New York time at the time zone of that date/time. We (persons) generally does not schedule appointments at DST start/end boundaries, but for the software that is more complicate. If the time 23:10:00 of a given date crosses a DST end boundary (at 00:00:00 clocks backwards an one hour), that time is ambiguous. So that should be another component of the time zone. PostgreSQL chooses to convert every time to UTC in disk and converts to session time zone in memory. It may be entered with a region, but it's discarded. Oracle implements a very good way IMO. It converts to UTC in disk but also store the region. In memory (SQL) time zones are preserved. Session time zone is used in conversions from string and non-timedzone types. Oracle also mentions inconsistency that may happen when the time zone database is updated. If user had previously entered a time in a moment that subsequently has updated by the time zone db, it may become inconsistent. So it should be updated manually. For me, from these three, only Oracle approach is usable. Adriano ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
