On 13/12/2017 15:20, Leyne, Sean wrote: >> 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. > I don't agree, offsets are the appropriate for all but boundary cases that > can be handled by appropriate application logic.
Offsets requires additional column (region) and logic to manage it. Considering standard way, where offset changes for the session time zone, it becomes near impossible to manage. > >> 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. > I agree, this is the best example. > > >> 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. > Also agree, but it is the responsibility of the calendar application to > determine the appropriate GMT offset that would apply 5 months from now, when > creating the row. > > It is not for the database to worry about DST rules, that is why the standard > says nothing about DST. > NoSQL people considers that manage transactions and referential integrity is the job of application, not the database. IMO it's the job of the database to manage TIMESTAMP WITH TIME ZONE correctly. >> 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. > But there are 2 type of schedules/meetings/appointments: > > 1- Meetings/appointments/Conf calls/Webinars that only apply to you or people > that are all located in the same time zone, so the local time context is > common/no mental math is required. I always works with this type of application, but I know it's definitively not the only type. > 2 - Conf Calls/Webinars (no other events can occur in multiple time zones). > In these cases, we schedule (set the start time) based on a current > understanding of what the time difference would apply to all members of the > conf calls/webinar. So, in this case mental math is required and can't be > avoided. In a global world, you can only choose a hour good for your main audience, not all, but this is not the point. The point is about one needing to manually (or via application) need to know what will be the timezone offset of a future date. In Brazil, for example, this depends on non obvious factors, for example, carnival end date, which is difficult to calculate. Previously, it was even worse the DST planning dates. This year our politics was speculating to not start DST just days before the initial date. > >> 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. > That is something the application needs to consider (or not*), not the > database. Not agree. > *MS Outlook allows me to schedule an event at 2:30am on Sunday March 11, > 2018. That date/time is invalid -- DST comes into effect that 2am that day. > > ** Interesting question, do MS SQL/PostgreSQL/Oracle report "2018-03-11 > 02:30:00 EST" as an invalid value? If not, what is reported by SELECT? I didn't tested. PostgreSQL seems to accept many things it shouldn't in my tests. Oracle by default accepts ambiguous dates resolving to the standard time. Invalid dates are rejected by default. > >> 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. > I have just reviewed the Oracle's "Datetime Datatypes and Time Zone Support" > doc > (https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006760) > and I didn't see any mention of Oracle storing the region. > "The TIMESTAMP WITH TIME ZONE datatype requires 13 bytes of storage, or two more bytes of storage than the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE datatypes because it stores time zone information. The time zone is stored as an offset from UTC or as a time zone region name." 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
