Re: [Firebird-devel] Time zone feature documentation
On 22/07/2018 13:14, Mark Rotteveel wrote: > On 11-5-2018 18:31, Adriano dos Santos Fernandes wrote: >> Hi! >> >> Here is the first README version for the time zone feature. >> >> https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md >> > > I just took another look, the format for isc_dpb_session_time_zone is > undocumented. Looking at the implementation it is a string like passed > to SET TIME ZONE, is that correct? > > I'd also like to know what the plans are for this: will it land in > Firebird 4? If so: when? > >From my part work is done in the branch. To merge it a decision and an action to update Windows ICU is necessary before the merge to master. 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
Re: [Firebird-devel] Time zone feature documentation
On 11-5-2018 18:31, Adriano dos Santos Fernandes wrote: Hi! Here is the first README version for the time zone feature. https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md I just took another look, the format for isc_dpb_session_time_zone is undocumented. Looking at the implementation it is a string like passed to SET TIME ZONE, is that correct? I'd also like to know what the plans are for this: will it land in Firebird 4? If so: when? Mark -- Mark Rotteveel -- 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
Re: [Firebird-devel] Time zone feature documentation
On 20/05/2018 08:25, Mark Rotteveel wrote: > On 11-5-2018 18:31, Adriano dos Santos Fernandes wrote: >> Hi! >> >> Here is the first README version for the time zone feature. >> >> https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md >> >> > > Will it be possible to set the session timezone on connect through a DPB > property? Sure, it's in my plan to add it, specially because it then is changed before ON CONNECT triggers. 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
Re: [Firebird-devel] Time zone feature documentation
On 11-5-2018 18:31, Adriano dos Santos Fernandes wrote: Hi! Here is the first README version for the time zone feature. https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Will it be possible to set the session timezone on connect through a DPB property? I like the flexibility of statements like `SET TIME ZONE`, but having to execute a number of statements immediately after connect to establish a consistent session configuration is overhead I'd prefer to have to do without. This question can be taken broader than just set time zone, as I guess it should also apply to things like the DECFLOAT bind and error configuration. Mark -- Mark Rotteveel -- 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
Re: [Firebird-devel] Time zone feature documentation
On 12/05/18 15:28, Adriano dos Santos Fernandes wrote: The discussion is going to a place that the next part will be about time zones as we know not works outside of Earth. The current offering is not practical in a number of current real world applications! And the same is true of other databases 'solutions' as well. As it currently stands it's no use to my applications so no reason to even bother testing it ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- 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
Re: [Firebird-devel] Time zone feature documentation
12.05.2018 16:24, Adriano dos Santos Fernandes wrote: In decode/encode functions what fractions are used? Is it Firebird legacy fractions in 1/1 of a second or standard fractions in 1/10 of a second? Same as with the WITHOUT tz types. Isn't it better to support a standard nine nines from the beginning that create a new API function later? -- WBR, SD. -- 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
Re: [Firebird-devel] Time zone feature documentation
On 12/05/2018 06:02, Lester Caine wrote: > > In part I can accept that, but short cuts need to be documented? It is > not common knowledge that TZ data IS questionable prior to 1970, and > with the amount of research being done on even just the second world > war, knowing that time one is normalizing needs knowledge of the > situation at that time. My digging into the German occupation of the > Channel Islands threw up the errors there with local time being adrift > from the rest of the UK! > > Dropping seconds accuracy and merging timezone into the same restricted > data field is a shortcut that has major limitations. First, prior to > standardised time we assume mid-day happened when the sun was directly > over head, so the rule for the whole world was LMT. Using Greenwich as a > timing point may only date back to 1675 and was only officially adopted > in 1880 but it is the current base. America joining in 3 years later and > the basis for timezones was established in 1884. So any date prior to > 1884 do not have timezones and are identified by their LMT offset. While > time prior to the introduction of accurate measuring devices may be > somewhat academic and millisecond accuracy totally pointless, that early > 19th century time had well documented differences of seconds. So any NEW > facility should at least permit those variations to be handled. Or > simply say 'the Firebird TIMEZONE extension is only accurate for dates > post 1970'. > > In my book a normalized time consists of FOUR elements. UTC based time, > offset, rule set, version. I don't think any of the current 'solutions' > offered by any database respects this and so even for CURRENT diary > events they have no way of flagging when the calculated offset has been > compromised. TZdist was an attempt to plug these holes, but has yet to > even start being used despite the fact that it is the perfect solution > for time management on 'internet devices'. I have also been advised of a > new ietf task group to address the Geolocate Extension and the Time Zone > Information Format bu these will be some time reaching an RFC stage :( > The discussion is going to a place that the next part will be about time zones as we know not works outside of Earth. 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
Re: [Firebird-devel] Time zone feature documentation
On 12/05/2018 05:51, Dimitry Sibiryakov wrote: > 11.05.2018 18:31, Adriano dos Santos Fernandes wrote: >> Here is the first README version for the time zone feature. > > In decode/encode functions what fractions are used? Is it Firebird > legacy fractions in 1/1 of a second or standard fractions in > 1/10 of a second? > Same as with the WITHOUT tz types. > I see no mentions of possible implicit/explicit CAST() conversions as > well as datatype coercion in DSQL. What will I get if fetch TIME WITH > TIME ZONE field using XSQLVAR.sqltype == SQL_TYPE_TIME? > The values in the session time zone. 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
Re: [Firebird-devel] Time zone feature documentation
On 12/05/18 07:43, Mark Rotteveel wrote: Like Macau, a lot of historic data has been added and for databases handling historic material it would be nice if there was a way to have matching correct offsets? Or we ignore the builtd in tools and cintinue to rely on external functions ... To be honest, I think the majority of our users will have no need for correct pre-1970 time zone data. I suspect none of the major database provide it either. Lets not get hung up on these esoteric parts of time zones. In part I can accept that, but short cuts need to be documented? It is not common knowledge that TZ data IS questionable prior to 1970, and with the amount of research being done on even just the second world war, knowing that time one is normalizing needs knowledge of the situation at that time. My digging into the German occupation of the Channel Islands threw up the errors there with local time being adrift from the rest of the UK! Dropping seconds accuracy and merging timezone into the same restricted data field is a shortcut that has major limitations. First, prior to standardised time we assume mid-day happened when the sun was directly over head, so the rule for the whole world was LMT. Using Greenwich as a timing point may only date back to 1675 and was only officially adopted in 1880 but it is the current base. America joining in 3 years later and the basis for timezones was established in 1884. So any date prior to 1884 do not have timezones and are identified by their LMT offset. While time prior to the introduction of accurate measuring devices may be somewhat academic and millisecond accuracy totally pointless, that early 19th century time had well documented differences of seconds. So any NEW facility should at least permit those variations to be handled. Or simply say 'the Firebird TIMEZONE extension is only accurate for dates post 1970'. In my book a normalized time consists of FOUR elements. UTC based time, offset, rule set, version. I don't think any of the current 'solutions' offered by any database respects this and so even for CURRENT diary events they have no way of flagging when the calculated offset has been compromised. TZdist was an attempt to plug these holes, but has yet to even start being used despite the fact that it is the perfect solution for time management on 'internet devices'. I have also been advised of a new ietf task group to address the Geolocate Extension and the Time Zone Information Format bu these will be some time reaching an RFC stage :( -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- 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
Re: [Firebird-devel] Time zone feature documentation
11.05.2018 18:31, Adriano dos Santos Fernandes wrote: Here is the first README version for the time zone feature. In decode/encode functions what fractions are used? Is it Firebird legacy fractions in 1/1 of a second or standard fractions in 1/10 of a second? I see no mentions of possible implicit/explicit CAST() conversions as well as datatype coercion in DSQL. What will I get if fetch TIME WITH TIME ZONE field using XSQLVAR.sqltype == SQL_TYPE_TIME? -- WBR, SD. -- 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
Re: [Firebird-devel] Time zone feature documentation
On 12-5-2018 02:25, Lester Caine wrote: On 12/05/18 00:32, Adriano dos Santos Fernandes wrote: On 11/05/2018 19:48, Lester Caine wrote: There is no information on those pages covering just what way the base TZ data is processed. I think it is probably safe to assume that it is unusable for providing timezone offsets prior to 1970 which is the current target of TZ itself, so it would be nice if there was a suitable warning somewhere? But some way to restore the pre 1970 data would be useful! ICU has the latest database here: http://source.icu-project.org/repos/icu/data/trunk/tzdata/icunew/2018e/ The original one:https://www.iana.org/time-zones (2018e from 2018-05-01). The IANA files mention back zones. If you have more accurate data, I'm sure it would be possible to contribute there. I think I have had confirmation that the stock ICU distribution does not include the backzone data, only the timezone id's that have been stripped in the zone1970.tab list. I have contributed data which you will see in the backzone file and which we know is correct, but instead we get data prior to 1970 for the wrong timezone and no indication that the data is wrong! Paul refuses to include pre-1970 data as it 'may be incorrect', but the result is that one has to assume ALL pre-1970 offsets are incorrect instead, despite people putting a lot of work into researching the missing pre-1970 data ... From TZ file history "A new file 'backzone' contains data which may appeal to connoisseurs of old time stamps, although it is out of scope for the tz database, is often poorly sourced, and contains some data that is known to be incorrect. The new file is not recommended for ordinary use and its entries are not installed by default. (Thanks to Lester Caine for the high-quality Jersey, Guernsey, and Isle of Man entries.)" Like Macau, a lot of historic data has been added and for databases handling historic material it would be nice if there was a way to have matching correct offsets? Or we ignore the builtd in tools and cintinue to rely on external functions ... To be honest, I think the majority of our users will have no need for correct pre-1970 time zone data. I suspect none of the major database provide it either. Lets not get hung up on these esoteric parts of time zones. Mark -- Mark Rotteveel -- 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
Re: [Firebird-devel] Time zone feature documentation
On 12/05/18 00:32, Adriano dos Santos Fernandes wrote: On 11/05/2018 19:48, Lester Caine wrote: There is no information on those pages covering just what way the base TZ data is processed. I think it is probably safe to assume that it is unusable for providing timezone offsets prior to 1970 which is the current target of TZ itself, so it would be nice if there was a suitable warning somewhere? But some way to restore the pre 1970 data would be useful! ICU has the latest database here: http://source.icu-project.org/repos/icu/data/trunk/tzdata/icunew/2018e/ The original one:https://www.iana.org/time-zones (2018e from 2018-05-01). The IANA files mention back zones. If you have more accurate data, I'm sure it would be possible to contribute there. I think I have had confirmation that the stock ICU distribution does not include the backzone data, only the timezone id's that have been stripped in the zone1970.tab list. I have contributed data which you will see in the backzone file and which we know is correct, but instead we get data prior to 1970 for the wrong timezone and no indication that the data is wrong! Paul refuses to include pre-1970 data as it 'may be incorrect', but the result is that one has to assume ALL pre-1970 offsets are incorrect instead, despite people putting a lot of work into researching the missing pre-1970 data ... From TZ file history "A new file 'backzone' contains data which may appeal to connoisseurs of old time stamps, although it is out of scope for the tz database, is often poorly sourced, and contains some data that is known to be incorrect. The new file is not recommended for ordinary use and its entries are not installed by default. (Thanks to Lester Caine for the high-quality Jersey, Guernsey, and Isle of Man entries.)" Like Macau, a lot of historic data has been added and for databases handling historic material it would be nice if there was a way to have matching correct offsets? Or we ignore the builtd in tools and cintinue to rely on external functions ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- 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
Re: [Firebird-devel] Time zone feature documentation
On 11/05/2018 19:48, Lester Caine wrote: > > There is no information on those pages covering just what way the base > TZ data is processed. I think it is probably safe to assume that it is > unusable for providing timezone offsets prior to 1970 which is the > current target of TZ itself, so it would be nice if there was a suitable > warning somewhere? But some way to restore the pre 1970 data would be > useful! > ICU has the latest database here: http://source.icu-project.org/repos/icu/data/trunk/tzdata/icunew/2018e/ The original one: https://www.iana.org/time-zones (2018e from 2018-05-01). The IANA files mention back zones. If you have more accurate data, I'm sure it would be possible to contribute there. 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
Re: [Firebird-devel] Time zone feature documentation
On 11/05/18 23:32, Vlad Khorsun via Firebird-devel wrote: 12.05.2018 1:12, Lester Caine wrote: On 11/05/18 20:37, Adriano dos Santos Fernandes wrote: * PST is Pitcairn or Pacific, with the same offset, they are considered different time zones ICU data is derived from TZ data hosted by IANA (http://site.icu-project.org/tznotice). I really can't answer questions about specific time zones, but really grateful if we can clarify them. The abbreviations are not defined to be unique. Only a subset of them can be used as identifiers, the rest are only 'defined' when associated with a particular rule, so are only suitable for displaying. Personally I need to see the different UK/Ireland timezone idents giving the right offsets prior to 1970 and that depends on how the ICU extract has been processed. It's not a case that 'the ICU data is derived from TZ data', it also depends on how the TZ data is processed by ICU and I'm not seeing ANY information on that. For the Irish area we then get IST standing for Irish Summer Time prior to 1968, and Irish Standard Time after that. Nothing is easy on any of this ... and IST is used in a number of other rules with similar differences in expansion. A quick scan of the ICU files would suggest 'IST' is used by them for India Standard Time but it would be nice if there was an easy way to confirm that? Certainly I'm not seeing data in zoneinfo64.txt for Isle of man, Jersey, Guernsey or Belfast so I suspect they are not valid pre-1970 :( Probably, this answers on (some of) your questions http://userguide.icu-project.org/datetime http://userguide.icu-project.org/datetime/calendar http://userguide.icu-project.org/datetime/timezone There is no information on those pages covering just what way the base TZ data is processed. I think it is probably safe to assume that it is unusable for providing timezone offsets prior to 1970 which is the current target of TZ itself, so it would be nice if there was a suitable warning somewhere? But some way to restore the pre 1970 data would be useful! -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- 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
Re: [Firebird-devel] Time zone feature documentation
12.05.2018 1:12, Lester Caine wrote: On 11/05/18 20:37, Adriano dos Santos Fernandes wrote: * PST is Pitcairn or Pacific, with the same offset, they are considered different time zones ICU data is derived from TZ data hosted by IANA (http://site.icu-project.org/tznotice). I really can't answer questions about specific time zones, but really grateful if we can clarify them. The abbreviations are not defined to be unique. Only a subset of them can be used as identifiers, the rest are only 'defined' when associated with a particular rule, so are only suitable for displaying. Personally I need to see the different UK/Ireland timezone idents giving the right offsets prior to 1970 and that depends on how the ICU extract has been processed. It's not a case that 'the ICU data is derived from TZ data', it also depends on how the TZ data is processed by ICU and I'm not seeing ANY information on that. For the Irish area we then get IST standing for Irish Summer Time prior to 1968, and Irish Standard Time after that. Nothing is easy on any of this ... and IST is used in a number of other rules with similar differences in expansion. A quick scan of the ICU files would suggest 'IST' is used by them for India Standard Time but it would be nice if there was an easy way to confirm that? Certainly I'm not seeing data in zoneinfo64.txt for Isle of man, Jersey, Guernsey or Belfast so I suspect they are not valid pre-1970 :( Probably, this answers on (some of) your questions http://userguide.icu-project.org/datetime http://userguide.icu-project.org/datetime/calendar http://userguide.icu-project.org/datetime/timezone Regards, Vlad -- 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
Re: [Firebird-devel] Time zone feature documentation
On 11/05/18 20:37, Adriano dos Santos Fernandes wrote: * PST is Pitcairn or Pacific, with the same offset, they are considered different time zones ICU data is derived from TZ data hosted by IANA (http://site.icu-project.org/tznotice). I really can't answer questions about specific time zones, but really grateful if we can clarify them. The abbreviations are not defined to be unique. Only a subset of them can be used as identifiers, the rest are only 'defined' when associated with a particular rule, so are only suitable for displaying. Personally I need to see the different UK/Ireland timezone idents giving the right offsets prior to 1970 and that depends on how the ICU extract has been processed. It's not a case that 'the ICU data is derived from TZ data', it also depends on how the TZ data is processed by ICU and I'm not seeing ANY information on that. For the Irish area we then get IST standing for Irish Summer Time prior to 1968, and Irish Standard Time after that. Nothing is easy on any of this ... and IST is used in a number of other rules with similar differences in expansion. A quick scan of the ICU files would suggest 'IST' is used by them for India Standard Time but it would be nice if there was an easy way to confirm that? Certainly I'm not seeing data in zoneinfo64.txt for Isle of man, Jersey, Guernsey or Belfast so I suspect they are not valid pre-1970 :( -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- 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
Re: [Firebird-devel] Time zone feature documentation
On 11/05/2018 16:04, Mark Rotteveel wrote: > On 11-5-2018 18:31, Adriano dos Santos Fernandes wrote: >> Hi! >> >> Here is the first README version for the time zone feature. >> >> https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md >> > > Reading through that, a few questions: > > # About the 2 bytes for the time zone identifier or displacement > > What is the valid range for displacement? Is it -12:00 - +14:00 (see > https://en.wikipedia.org/wiki/List_of_UTC_time_offsets) or something > else? If so, why the +1439 in the calculation of the byte value? It > seems to me the current possible range for the byte value is 719 - > 2279 (-12 * 60 + 1439 = 719 to 14*60 + 1439 = 2279). > The accepted range when parsing a time zone string/literal is from -14:00 to 14:00. This limit is present in the standard. > The choice of 1439 seems to suggest an expectation of -23:59 - +23:59 > (or 0 - 2878), why? > I want to make all of this range reserved, as in a case it must be increased. > Will Firebird guarantee that the timezone identifiers will be stable > (that is: a timezone will not get a different id in a new version or > ICU release?), or would a driver always need to query the table with > time zones instead of keeping its own list? > The ids are defined in Firebird, not in ICU, so they are stable. > # Set time zone local > > What does this mean? Does it mean set to server time zone? > If run from the client (currently, if run anywhere), will reset to the default time zone, which in our case is the server time zone of the engine process. The standard is more complex on this command. It seems each routine creates a "stack" of the current time zone, so when set it to LOCAL it resets to the value it was when the routine started. > # Time zone identifiers > > How are ambiguities handled: > > For example: > Which time zone is ACT (id 65534)? Is it Acre Time (-05:00) or > Australian Central Time which is an alias for ACST/ACDT (+09:30 / > +10:30)? > > Which time zone is IST (id 65024)? Is it India Standard Time (+5:30) > or Irish Standard/Summer Time (+01:00) or Israel Standard Time (+02:00) > > And what about AST (65530), BST (65162), CST (65154), PST* (64988)? > > Interestingly a number of duplicates (according to > https://www.timeanddate.com/time/zones/) are not included in the list: > ADT, AMST, AMT, CDT, GST, PYT, WST > > I notice that 'summer' time zones are not present. For example, CET > (Central European Time, +01:00) is present, but CEST (Central European > Summer Time, +02:00) is absent. Does this mean that CET represents > both summer and winter time? > > * PST is Pitcairn or Pacific, with the same offset, they are > considered different time zones > ICU data is derived from TZ data hosted by IANA (http://site.icu-project.org/tznotice). I really can't answer questions about specific time zones, but really grateful if we can clarify them. 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
Re: [Firebird-devel] Time zone feature documentation
On 11-5-2018 18:31, Adriano dos Santos Fernandes wrote: Hi! Here is the first README version for the time zone feature. https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Reading through that, a few questions: # About the 2 bytes for the time zone identifier or displacement What is the valid range for displacement? Is it -12:00 - +14:00 (see https://en.wikipedia.org/wiki/List_of_UTC_time_offsets) or something else? If so, why the +1439 in the calculation of the byte value? It seems to me the current possible range for the byte value is 719 - 2279 (-12 * 60 + 1439 = 719 to 14*60 + 1439 = 2279). The choice of 1439 seems to suggest an expectation of -23:59 - +23:59 (or 0 - 2878), why? Will Firebird guarantee that the timezone identifiers will be stable (that is: a timezone will not get a different id in a new version or ICU release?), or would a driver always need to query the table with time zones instead of keeping its own list? # Set time zone local What does this mean? Does it mean set to server time zone? # Time zone identifiers How are ambiguities handled: For example: Which time zone is ACT (id 65534)? Is it Acre Time (-05:00) or Australian Central Time which is an alias for ACST/ACDT (+09:30 / +10:30)? Which time zone is IST (id 65024)? Is it India Standard Time (+5:30) or Irish Standard/Summer Time (+01:00) or Israel Standard Time (+02:00) And what about AST (65530), BST (65162), CST (65154), PST* (64988)? Interestingly a number of duplicates (according to https://www.timeanddate.com/time/zones/) are not included in the list: ADT, AMST, AMT, CDT, GST, PYT, WST I notice that 'summer' time zones are not present. For example, CET (Central European Time, +01:00) is present, but CEST (Central European Summer Time, +02:00) is absent. Does this mean that CET represents both summer and winter time? * PST is Pitcairn or Pacific, with the same offset, they are considered different time zones -- Mark Rotteveel -- 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
Re: [Firebird-devel] Time zone feature documentation
On 11/05/18 17:31, Adriano dos Santos Fernandes wrote: Here is the first README version for the time zone feature. https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md So there is no provision to support the initial LMT offsets for those rule sets that provide them? These are second accuracy and for example the new rules for Asia/Macau moved that one from 7:34:20 in 1911 to 7:34:10 in 1904. That is the first time that region started using a centralised time offset rather than Local Mean Time. Most timezones have a second's accurate initial offset, and some have a number of them as the 'novelty' of railway and similar times changed to more centralised and then national times. Reading between the lines, the rule sets used are those provided by ICU rather than the the Oracle approach of providing it's own external files built from TZ distribution versions? Only the full TZ/backzone table with it's own numeric mapping is compiled in the source code? So it is the ICU library that needs hacking to provide earlier views of the rule sets? Has a way of identifying just which rule set is being returned been identified so we can query it and store it with the data? (I'm currently using PHP's library to handle timezone data - and only returning UTC normalized values to the database) Another restriction on timestamps could do with being addressed. Historic genealogical dates can predate January 01 1 so it is quite common to switch to a different date/time library based on unix epoch anyway to make managing the data easier ... this is of cause always a UTC value. It's been so long since I made that switch I'd forgotten that many fields are bigint now rather than timestamp anyway. If time permitted I would build my own TZdist server using Firebird as the database and managing the history of TZ changes in a way that one could identify where normalised data needs refreshing. All the data is readily available and the API for TZdist while cumbersome is elegant when supplying raw offset data to work with. It SHOULD be able to identify when an event's UTC time has changed due to a short notice change of offsets and does not need to be restricted to the limitations of the TZ release process. https://tools.ietf.org/html/rfc7808 if you have not seen it. It provides nice tidy definitions of many of the key terms. It also fails to define just how time zone names are created3 leaving that to the 'publisher'. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- 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
[Firebird-devel] Time zone feature documentation
Hi! Here is the first README version for the time zone feature. https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md 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
