> 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. > 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. > 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. 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. > 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. *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? > 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. Sean ------------------------------------------------------------------------------ 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
