> 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.
Let's clarify, "Region" refers to IANA approved timezone name/abbreviation (i.e. "America/Toronto" / "EST"*) "Offset" refers to the UTC offset (i.e. -5) which would be stored with the TIMESTAMP * I find it interesting that IANA describes "EST" are a deprecated timezone > Considering standard way, where offset changes for the session time zone, it > becomes near impossible to manage. I agree that there are challenges, but I see the same for the Oracle approach IMO, the PostgreSQL approach, while more basic, provides context that a developer that work with. Separately, consider that calendar applications exchange times using UTC offset contexts not time zone/region names. Why? Local Timezone UTC offsets and DST rules are *variable*, UTC offsets are not. > >> 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. Let's stick to stuck that relates to real databases (the SQL standard) > > 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. That is not a database issue, it is the reality of how meetings are scheduled across timezones. (I have this problem when I setup calls to clients in India, Singapore or Australia) > 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. Let's discuss that example further. So, what do you expect the impact of the change to the DST would be for an applications. - You (in BrasÃlia) setup a phone call/meeting, 6 months ago, for today at 7am (Dec 12, 2017 == UTC -2) with customers in Mumbai (5:30pm -- UTC +5:30) and Adelaide Australia ( 10:30pm -- UTC +10:30) - Each of you have an initial common definition of the meeting time. - Imagine that the Brazilian government decided on October 1st that, as of December 1st, the entire country is changing to UTC = -3. What do you expect to happen to the meeting time? The only way that the both parties have the correct meeting times is if: 1- both parties have a timezone database which is exactly sync'd 2- the meeting Date/Time is stored with offset == -2. Yes, I realize that this would result your time of the meeting changing, that is your problem -- blame your government -- but the change in would be consistent for your entire schedule, but your customer would be unaffected. > >> 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.ht > m#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." Thanks for pointer 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
