DST ends 2040 in Oracle database
Hello, we found one issue in the Oracle database that DST calculation is wrong from 2040 and beyond. I create a SR at Oracle and the Oracle support told me, that this behavior is from the IANA tzdata file and not from Oracle. My question is, if this is the case, where can I find the required information? If this is not the case, please let me also know to give the information to the Oracle support. I will appreciate your feedback regarding the information from the Oracle SR team! Mit freundlichen Grüßen / Kind Regards i.A. Joachim Damm Senior Database Consultant Systems Integration [cid:image001.jpg@01CF7F53.FD0881C0] Werum IT Solutions GmbH Wulf-Werum-Str. 3, 21337 Lüneburg, Germany T +49 4131 8900-515 F +49 4131 8900-20 Joachim.Damm@Werum.com<mailto:Joachim.Damm@Werum.com> www.werum.com<http://www.werum.com/> Geschäftsführer / Managing Directors: Rüdiger Schlierenkämper, Richard Nagorny, Hans-Peter Subel RG Lüneburg / Court of Jurisdiction: Lüneburg, Germany Handelsregisternummer / Commercial Register: HRB 204984 USt.-IdNr. / VAT No.: DE 116 083 850 Connect with us: [cid:image006.jpg@01D4B3D4.B9323120] <https://www.linkedin.com/company/werum> [cid:image007.jpg@01D4B3D4.B9323120] <https://twitter.com/werum> [cid:image008.jpg@01D4B3D4.B9323120] <http://www.vimeo.com/werum> [cid:image009.jpg@01D4B3D4.B9323120] <https://www.youtube.com/werum>
On 1/24/19 2:05 AM, Joachim Damm wrote:
we found one issue in the Oracle database that DST calculation is wrong from 2040 and beyond.
Which DST calculation, exactly, and what are the incorrect and correct values? Perhaps you're thinking about Brazil, Iran, or Morocco. In these countries DST rules are so complex that they cannot be expressed in closed form in the tzdb notation, so tzdb lists rules explicitly for each year. Eventually this list has to stop, though, as the database and its maintainers' patience are finite. For Brazil and Morocco the list of exact predictions stops after 2037; for Iran, 2087. Brazil and Morocco keep changing their DST rules, so any prediction past this year (much less past 2037) is dubious anyway. In contrast, Iran's rules have been stable since 2008, so I extended its exact prediction to 2087; there is some technical confusion about how to interpret Iran's rules after that, and even Iran is likely to change its rules before 2087. If there's a real need to predict past 2037, what is the need and how far does it really go?
On 2019-01-24 11:30, Paul Eggert wrote:
On 1/24/19 2:05 AM, Joachim Damm wrote:
we found one issue in the Oracle database that DST calculation is wrong from 2040 and beyond.
Which DST calculation, exactly, and what are the incorrect and correct values?
Perhaps you're thinking about Brazil, Iran, or Morocco. In these countries DST rules are so complex that they cannot be expressed in closed form in the tzdb notation, so tzdb lists rules explicitly for each year. Eventually this list has to stop, though, as the database and its maintainers' patience are finite. For Brazil and Morocco the list of exact predictions stops after 2037; for Iran, 2087.
Brazil and Morocco keep changing their DST rules, so any prediction past this year (much less past 2037) is dubious anyway. In contrast, Iran's rules have been stable since 2008, so I extended its exact prediction to 2087; there is some technical confusion about how to interpret Iran's rules after that, and even Iran is likely to change its rules before 2087.
If there's a real need to predict past 2037, what is the need and how far does it really go?
Looks like tzdb supported 64 bit time_t from [19]95f, with updates in 2004h and 2006b, when support for 64 bit tzdata was added. Oracle is old so may only support 32 bit tzdata up to 2038 for compatibility: as with Java tzdb issues, they are due to Oracle design choices. -- Take care. Thanks, Brian Inglis, Calgary, Alberta, Canada This email may be disturbing to some readers as it contains too much technical detail. Reader discretion is advised.
This seems to be a design decision, indeed. Our internal compiler generates explicit transition rules up to year 2040 for all time zones with open-ended rules. The code comment references original zic compiler and its old 2038 limit. I am checking internally if there is more to the current design. Thanks, Sergiusz (Oracle Database Development) On Thu, Jan 24, 2019 at 11:54 PM Brian Inglis < Brian.Inglis@systematicsw.ab.ca> wrote:
On 2019-01-24 11:30, Paul Eggert wrote:
On 1/24/19 2:05 AM, Joachim Damm wrote:
we found one issue in the Oracle database that DST calculation is wrong from 2040 and beyond.
Which DST calculation, exactly, and what are the incorrect and correct values?
Perhaps you're thinking about Brazil, Iran, or Morocco. In these countries DST rules are so complex that they cannot be expressed in closed form in the tzdb notation, so tzdb lists rules explicitly for each year. Eventually this list has to stop, though, as the database and its maintainers' patience are finite. For Brazil and Morocco the list of exact predictions stops after 2037; for Iran, 2087.
Brazil and Morocco keep changing their DST rules, so any prediction past this year (much less past 2037) is dubious anyway. In contrast, Iran's rules have been stable since 2008, so I extended its exact prediction to 2087; there is some technical confusion about how to interpret Iran's rules after that, and even Iran is likely to change its rules before 2087.
If there's a real need to predict past 2037, what is the need and how far does it really go?
Looks like tzdb supported 64 bit time_t from [19]95f, with updates in 2004h and 2006b, when support for 64 bit tzdata was added. Oracle is old so may only support 32 bit tzdata up to 2038 for compatibility: as with Java tzdb issues, they are due to Oracle design choices.
-- Take care. Thanks, Brian Inglis, Calgary, Alberta, Canada
This email may be disturbing to some readers as it contains too much technical detail. Reader discretion is advised.
On 2019-01-24 21:16, Sergiusz Wolicki wrote:
On Thu, Jan 24, 2019 at 11:54 PM Brian Inglis wrote:
On 2019-01-24 11:30, Paul Eggert wrote:
On 1/24/19 2:05 AM, Joachim Damm wrote:
we found one issue in the Oracle database that DST calculation is wrong from 2040 and beyond.>>> Which DST calculation, exactly, and what are the incorrect and correct values? Perhaps you're thinking about Brazil, Iran, or Morocco. In these countries DST rules are so complex that they cannot be expressed in closed form in the tzdb notation, so tzdb lists rules explicitly for each year. Eventually this list has to stop, though, as the database and its maintainers' patience are finite. For Brazil and Morocco the list of exact predictions stops after 2037; for Iran, 2087.>>> Brazil and Morocco keep changing their DST rules, so any prediction past this year (much less past 2037) is dubious anyway. In contrast, Iran's rules have been stable since 2008, so I extended its exact prediction to 2087; there is some technical confusion about how to interpret Iran's rules after that, and even Iran is likely to change its rules before 2087.>>> If there's a real need to predict past 2037, what is the need and how far does it really go?>> Looks like tzdb supported 64 bit time_t from [19]95f, with updates in 2004h and 2006b, when support for 64 bit tzdata was added.>> Oracle is old so may only support 32 bit tzdata up to 2038 for compatibility: as with Java tzdb issues, they are due to Oracle design choices.> This seems to be a design decision, indeed. Our internal compiler generates explicit transition rules up to year 2040 for all time zones with open-ended rules. The code comment references original zic compiler and its old 2038 limit.> I am checking internally if there is more to the current design. Please bear in mind that tzdb 64 bit data generated by zic now ranges from -BIGBANG to +BIGBANG, or +400 years in the future if the rules are not perpetual, when considering possible extension approaches.
[I have only looked at Oracle time zones for my own interest, not across the whole date time range, or actually used them in production; the few thousand instances at various gigs standardized on local server date time stamps in all apps, often in US locales, so date time display was a GUI or reporting function.] -- Take care. Thanks, Brian Inglis, Calgary, Alberta, Canada This email may be disturbing to some readers as it contains too much technical detail. Reader discretion is advised.
Dear Paul, I am looking for America/New_York and I got the information from Oracle that tzdata from IANA are almost till 2040. My question is, is that correct? And, if this is correct, is there any plan to fix this? For the reason of my question, we are a software company and in our software the customer is able to calculate an expiry date. Now, the first customer (in the US) build a product with an expiry date in 2046 and we see, that the calculation from Oracle is wrong. I have created a SR at Oracle and the support tell me the following: "Please note that Oracle database pick up tzdata from IANA and the tzdata available are mostly till 2040 only. As mentioned earlier the DST patches contains rules till 2040, as of now which needs to be extended DST files past the year 2040 This is known issue which will be fix in future with below enhancement request raised." Can you confirm the statement from Oracle or is it wrong? Mit freundlichen Grüßen / Kind Regards i.A. Joachim Damm Senior Database Consultant Systems Integration Werum IT Solutions GmbH Wulf-Werum-Str. 3, 21337 Lüneburg, Germany T +49 4131 8900-515 F +49 4131 8900-20 Joachim.Damm@Werum.com www.werum.com Geschäftsführer / Managing Directors: Rüdiger Schlierenkämper, Richard Nagorny, Hans-Peter Subel RG Lüneburg / Court of Jurisdiction: Lüneburg, Germany Handelsregisternummer / Commercial Register: HRB 204984 USt.-IdNr. / VAT No.: DE 116 083 850 Connect with us: -----Original Message----- From: Paul Eggert [mailto:eggert@cs.ucla.edu] Sent: Donnerstag, 24. Januar 2019 19:31 To: Joachim Damm <Joachim.Damm@werum.com>; Time Zone Mailing List <tz@iana.org> Subject: Re: [tz] DST ends 2040 in Oracle database On 1/24/19 2:05 AM, Joachim Damm wrote:
we found one issue in the Oracle database that DST calculation is wrong from 2040 and beyond.
Which DST calculation, exactly, and what are the incorrect and correct values? Perhaps you're thinking about Brazil, Iran, or Morocco. In these countries DST rules are so complex that they cannot be expressed in closed form in the tzdb notation, so tzdb lists rules explicitly for each year. Eventually this list has to stop, though, as the database and its maintainers' patience are finite. For Brazil and Morocco the list of exact predictions stops after 2037; for Iran, 2087. Brazil and Morocco keep changing their DST rules, so any prediction past this year (much less past 2037) is dubious anyway. In contrast, Iran's rules have been stable since 2008, so I extended its exact prediction to 2087; there is some technical confusion about how to interpret Iran's rules after that, and even Iran is likely to change its rules before 2087. If there's a real need to predict past 2037, what is the need and how far does it really go?
Although tzdb's traditional .zi (text) files for New York have no expiration date, its traditional TZif (binary) format was limited to signed 32-bit timestamps and so stopped working after 2038. As Brian Inglis noted here: https://mm.icann.org/pipermail/tz/2019-January/027431.html the TZif year-2038 problem was fixed in 2006. However, as Sergiusz Wolicki noted here: https://mm.icann.org/pipermail/tz/2019-January/027432.html the Oracle compiler stops at 2040, inspired by the earlier TZif limit. So if you need to predict timestamps past 2040 you'll need to fix the Oracle compiler, or use some other compiler to translate the .zi files.
On 2019-01-25 12:46, Paul Eggert wrote:
Although tzdb's traditional .zi (text) files for New York have no expiration date, its traditional TZif (binary) format was limited to signed 32-bit timestamps and so stopped working after 2038. As Brian Inglis noted here:
https://mm.icann.org/pipermail/tz/2019-January/027431.html
the TZif year-2038 problem was fixed in 2006. However, as Sergiusz Wolicki noted here:
https://mm.icann.org/pipermail/tz/2019-January/027432.html
the Oracle compiler stops at 2040, inspired by the earlier TZif limit. So if you need to predict timestamps past 2040 you'll need to fix the Oracle compiler, or use some other compiler to translate the .zi files.
I believe the Oracle tzdb compiler is an internal tool which generates time zone files which can be downloaded as patches for RDBMS DSTv?? with a support contract from the support site. https://docs.oracle.com/en/database/oracle/oracle-database/18/nlspg/datetime... says: "Note: Oracle Database time zone data is derived from the public domain information available on The IANA Functions website. Oracle Database time zone data may not reflect the most recent data available on this website." The link to Oracle TZ and DST patches is in: https://support.oracle.com/knowledge/Oracle%20Database%20Products/412160_1.h... How to check new tz files with DB upgrade script utltz_upg_check.sql, and make changes with DB upgrade script utltz_upg_apply.sql is in: https://docs.oracle.com/en/database/oracle/oracle-database/18/nlspg/datetime... How to use those scripts at a larger site: https://mikedietrichde.com/2018/12/18/how-to-patch-all-pdbs-with-the-a-new-t... -- Take care. Thanks, Brian Inglis, Calgary, Alberta, Canada This email may be disturbing to some readers as it contains too much technical detail. Reader discretion is advised.
The Oracle compiler is an internal tool and the binary timezone files are in an Oracle-internal format as well. Therefore, if the current Oracle behavior is to change, this must be done by Oracle. However, there is a possible performance trade-off in supporting more years or supporting open-ended rules. An Oracle database processes potentially massive amount of data and any slowdown in date processing may create a significant regression in total query response times for most customers who do not need dates over 20 years into the future. Therefore, this issue needs to be evaluated carefully. Moreover, I would like to point out that from practical point of view, this does not look like a real problem for the use case given. When an expire date is 27 years into the future who cares if it is one hour less or one hour more? I would expect expire dates so far in the future to be without time component anyway. To give an advice for a workaround I would need to understand why the time component is needed and how you account for the fact that DST rules in 20 years may be different from current ones anyway, so whatever you see today may be different from what you see after a DB upgrade in a few years. (See EU plans to abandon DST.) Thanks, Sergiusz (Oracle Database Development) P.S. Please note that I am not an authorized speaker for Oracle. Opinions expressed here are my own. On Fri, Jan 25, 2019 at 8:47 PM Paul Eggert <eggert@cs.ucla.edu> wrote:
Although tzdb's traditional .zi (text) files for New York have no expiration date, its traditional TZif (binary) format was limited to signed 32-bit timestamps and so stopped working after 2038. As Brian Inglis noted here:
https://mm.icann.org/pipermail/tz/2019-January/027431.html
the TZif year-2038 problem was fixed in 2006. However, as Sergiusz Wolicki noted here:
https://mm.icann.org/pipermail/tz/2019-January/027432.html
the Oracle compiler stops at 2040, inspired by the earlier TZif limit. So if you need to predict timestamps past 2040 you'll need to fix the Oracle compiler, or use some other compiler to translate the .zi files.
On 1/28/19 9:26 AM, Sergiusz Wolicki wrote:
Moreover, I would like to point out that from practical point of view, this does not look like a real problem for the use case given. When an expire date is 27 years into the future who cares if it is one hour less or one hour more?
Yes, problems in this area are often software glitches that reflect fundamental misunderstanding of predicting future timestamps (or guessing past ones). For example, the software might be told something like "This bond payment is due at 17:00 Morocco time on January 28, 2029", one program uses tzdb 2018i and another program uses tzdb 2018g to convert that timestamp to UTC internally, and their internal UTC timestamps disagree. Here the real bug is assuming that an operation like "convert from Morocco time to UTC" is a portable and repeatable operation, which it certainly is not for future timestamps, and sometimes not even for past timestamps.
Except it is already year 2019 and 2038 January 19 is now only less than 19 years into the future. If one get a 20-year mortgage now, the final payment day/time will already be after 2038. It's probably about time for developers and vendors to consider the problem more seriously. 2019-1-29 01:27, Sergiusz Wolicki <sergiusz@wolicki.com> wrote:
The Oracle compiler is an internal tool and the binary timezone files are in an Oracle-internal format as well. Therefore, if the current Oracle behavior is to change, this must be done by Oracle. However, there is a possible performance trade-off in supporting more years or supporting open-ended rules. An Oracle database processes potentially massive amount of data and any slowdown in date processing may create a significant regression in total query response times for most customers who do not need dates over 20 years into the future. Therefore, this issue needs to be evaluated carefully.
Moreover, I would like to point out that from practical point of view, this does not look like a real problem for the use case given. When an expire date is 27 years into the future who cares if it is one hour less or one hour more? I would expect expire dates so far in the future to be without time component anyway. To give an advice for a workaround I would need to understand why the time component is needed and how you account for the fact that DST rules in 20 years may be different from current ones anyway, so whatever you see today may be different from what you see after a DB upgrade in a few years. (See EU plans to abandon DST.)
Thanks, Sergiusz (Oracle Database Development)
P.S. Please note that I am not an authorized speaker for Oracle. Opinions expressed here are my own.
On Fri, Jan 25, 2019 at 8:47 PM Paul Eggert <eggert@cs.ucla.edu> wrote:
Although tzdb's traditional .zi (text) files for New York have no expiration date, its traditional TZif (binary) format was limited to signed 32-bit timestamps and so stopped working after 2038. As Brian Inglis noted here:
https://mm.icann.org/pipermail/tz/2019-January/027431.html
the TZif year-2038 problem was fixed in 2006. However, as Sergiusz Wolicki noted here:
https://mm.icann.org/pipermail/tz/2019-January/027432.html
the Oracle compiler stops at 2040, inspired by the earlier TZif limit. So if you need to predict timestamps past 2040 you'll need to fix the Oracle compiler, or use some other compiler to translate the .zi files.
Date: Tue, 29 Jan 2019 12:13:47 +0800 From: Phake Nick <c933103@gmail.com> Message-ID: <CAGHjPPL=8HkNyvdHAqJRAd48Dso37n11sx_5DP1Aygxs70A7Rg@mail.gmail.com> | Except it is already year 2019 and 2038 January 19 is now only less than 19 | years into the future. If one get a 20-year mortgage now, the final payment | day/time will already be after 2038. It's probably about time for | developers and vendors to consider the problem more seriously. Perhaps, but if you haven't already done so, you need to read Paul's message... The developers who need to take this kind of issue seriously are the ones (to use your example) who are recoding the mortgage end date/time. If your mortgage were in New York (probably a huge one, and 20 years might not be enough...) then the end date time of that 20 year mortgage should be recorded as "5:00 p.m. on the January 29, 2039, in New York City". If your mortgage is in Hong Kong (might need to be even longer than for New York!) the end date time should be recorded as "17:00 on 29th of January, 2039, in Hong Kong". In each case the place might need to be more specific (that is the final payment might need to be in a specific room in a specific building or something, fo facilitate the document echange, or perhaps "at a location to be agreed", but that isn't relevant to this discussion.) Under no circumstances (other than specific agreement by the parties) should it be recorded as "2039-01-29 21:00:00 UTC" or "2030-01-29 09:00:00 UTC" (if I did the conversions in my head correctly) - that would always be wrong (again unless that's what was specifically agreed - the person/software recording the end time should *never* make that kind of change "just because UTC is always better". kre
Hi, only for clarifying. Our software is for the pharmaceutical industry and there it is not acceptable if the expiry date of an active ingredient is calculated wrongly. The FDA (US Food and Drug Administration) will also no accept wrong timestamps in the production, also not in the distant future.
From the Oracle support I got the information that they are internally checking with their DST experts. The former information from Oracle was, that this is a known issue which will be fix in future with below enhancement request raised.
Kind Regards i.A. Joachim Damm Senior Database Consultant Systems Integration Werum IT Solutions GmbH Wulf-Werum-Str. 3, 21337 Lüneburg, Germany T +49 4131 8900-515 F +49 4131 8900-20 Joachim.Damm@Werum.com www.werum.com Geschäftsführer / Managing Directors: Rüdiger Schlierenkämper, Richard Nagorny, Hans-Peter Subel RG Lüneburg / Court of Jurisdiction: Lüneburg, Germany Handelsregisternummer / Commercial Register: HRB 204984 USt.-IdNr. / VAT No.: DE 116 083 850 Connect with us: -----Original Message----- From: Robert Elz [mailto:kre@munnari.OZ.AU] Sent: Dienstag, 29. Januar 2019 07:43 To: Phake Nick <c933103@gmail.com> Cc: Sergiusz Wolicki <sergiusz@wolicki.com>; Time zone mailing list <tz@iana.org>; Joachim Damm <Joachim.Damm@werum.com> Subject: Re: [tz] DST ends 2040 in Oracle database Date: Tue, 29 Jan 2019 12:13:47 +0800 From: Phake Nick <c933103@gmail.com> Message-ID: <CAGHjPPL=8HkNyvdHAqJRAd48Dso37n11sx_5DP1Aygxs70A7Rg@mail.gmail.com> | Except it is already year 2019 and 2038 January 19 is now only less than 19 | years into the future. If one get a 20-year mortgage now, the final payment | day/time will already be after 2038. It's probably about time for | developers and vendors to consider the problem more seriously. Perhaps, but if you haven't already done so, you need to read Paul's message... The developers who need to take this kind of issue seriously are the ones (to use your example) who are recoding the mortgage end date/time. If your mortgage were in New York (probably a huge one, and 20 years might not be enough...) then the end date time of that 20 year mortgage should be recorded as "5:00 p.m. on the January 29, 2039, in New York City". If your mortgage is in Hong Kong (might need to be even longer than for New York!) the end date time should be recorded as "17:00 on 29th of January, 2039, in Hong Kong". In each case the place might need to be more specific (that is the final payment might need to be in a specific room in a specific building or something, fo facilitate the document echange, or perhaps "at a location to be agreed", but that isn't relevant to this discussion.) Under no circumstances (other than specific agreement by the parties) should it be recorded as "2039-01-29 21:00:00 UTC" or "2030-01-29 09:00:00 UTC" (if I did the conversions in my head correctly) - that would always be wrong (again unless that's what was specifically agreed - the person/software recording the end time should *never* make that kind of change "just because UTC is always better". kre
Joachim Damm wrote:
Our software is for the pharmaceutical industry and there it is not acceptable if the expiry date of an active ingredient is calculated wrongly.
Not many drugs made today have an expiration date past 2039. Also, if an ingredient labeled "expires 08/07/2019" is made in Puerto Rico and used in Germany, do the applicable FDA regulations say that the date should be interpreted in Puerto Rico time or in German time? If the latter, does this mean that if (say) Germany cancels DST, then such an ingredient would expire one real-time hour later than it would otherwise? PS. When I was younger one of my jobs was to go through the drugs in a pharmacy, check their expiration dates, and toss the outdated ones. Who knew I'd still be worrying about this stuff years later? :-)
Date: Tue, 29 Jan 2019 23:32:45 -0800 From: Paul Eggert <eggert@cs.ucla.edu> Message-ID: <96498127-37cf-e097-2b18-10864f7a0edf@cs.ucla.edu> | Also, if an ingredient labeled "expires 08/07/2019" is made in Puerto Rico and | used in Germany, do the applicable FDA regulations say that the date should be | interpreted in Puerto Rico time or in German time? Bigger problem, does that mean the 8th of July or the 7th of August ? In Puerto Rico, that would probably be the latter, but in Germany quite possibly the former, which would have them being used for a month after their intended expiry. Most drugs I see have the expiry date listed as (just) month and year (perhaps unless they have a very short shelf life) - but for anything that is years away from the manufaacture date is anyone (FDA included) really asking anyone to believe that one day here or there is going to make a difference, especially as environmental conditions are most likely not well controlled. The hour (or two) of time variation for summer time is never going to be relevant. kre
On Jan 30, 2019, at 07:17, Robert Elz <kre@munnari.OZ.AU> wrote:
Most drugs I see have the expiry date listed as (just) month and year (perhaps unless they have a very short shelf life) - but for anything that is years away from the manufaacture date is anyone (FDA included) really asking anyone to believe that one day here or there is going to make a difference, especially as environmental conditions are most likely not well controlled. The hour (or two) of time variation for summer time is never going to be relevant.
Unfortunately, you’re likely dealing with attorneys here. Straining at gnats and swallowing elephants has been a required course in law schools since at least 33 AD. Cheers! |---------------------------------------------------------------------| | Frederick F. Gleason, Jr. | Chief Developer | | | Paravel Systems | |---------------------------------------------------------------------| | Some men are heterosexual, and some are bisexual, and some men | | don't think about sex at all... they become lawyers. | | | | -- Woody Allen | |---------------------------------------------------------------------|
On 2019-01-30 06:30, Fred Gleason wrote:
On Jan 30, 2019, at 07:17, Robert Elz wrote:
Most drugs I see have the expiry date listed as (just) month and year (perhaps unless they have a very short shelf life) - but for anything that is years away from the manufaacture date is anyone (FDA included) really asking anyone to believe that one day here or there is going to make a difference, especially as environmental conditions are most likely not well controlled. The hour (or two) of time variation for summer time is never going to be relevant. Unfortunately, you’re likely dealing with attorneys here. Straining at gnats and swallowing elephants has been a required course in law schools since at least 33 AD. | Some men are heterosexual, and some are bisexual, and some men | | don't think about sex at all... they become lawyers. | | -- Woody Allen |
Don't talk too loudly: they may not agree with times other than solar LMT or maybe GMT with an offset; anything that is not defined as legal time in a law in a specific jurisdiction is a basis for a legal/rhetorical argument. Certainly expiry day of the month and time of day would be grist to the mill in liability lawsuits. Some payment processors will still not accept credit cards with expiry dates in the current month, despite credit card issuer standards stating that expiry is at the end of the month (in which or whose time zone?) -- Take care. Thanks, Brian Inglis, Calgary, Alberta, Canada This email may be disturbing to some readers as it contains too much technical detail. Reader discretion is advised.
On 1/30/19 4:17 AM, Robert Elz wrote:
Bigger problem, does that mean the 8th of July or the 7th of August ?
Yes, I picked "08/07/2019" for that reason. It's in US style appropriate for Puerto Rico, whereas the same date in typical German style would be "07.08.2019". If I were in charge of printing those dates, I'd use the international style "2019-08-07" to help avoid this ambiguity. The FDA regulations for drug expiration dating do not specify a date format, nor do they specify a resolution or whether UTC or local time should be used. A label can say just "Expires 2021" and apparently that's good enough. (It'd probably be pushing the envelope for a label to say "Expires in the third millennium".) See: https://www.ecfr.gov/cgi-bin/text-idx?node=se21.4.211_1137
for anything that is years away from the manufaacture date is anyone (FDA included) really asking anyone to believe that one day here or there is going to make a difference, especially as environmental conditions are most likely not well controlled. The hour (or two) of time variation for summer time is never going to be relevant.
Absolutely. In practice expiration dates are a big deal for only a few drugs: tetracycline, liquid antibiotics, insulin, nitroglycerin, epi pens, thyroid, blood thinners, some eye drops, and a few others. For most drugs, you can use stuff that is several years expired and you'll be fine. (If you're not expert in a drug, of course it's best to play safe and throw out expired drugs.) For more, see: Allen M. The myth of drug expiration dates. Pro Publica. 2017-07-18 05:00 -05. https://www.propublica.org/article/the-myth-of-drug-expiration-dates
On 2019-01-30 16:50, Paul Eggert wrote:
On 1/30/19 4:17 AM, Robert Elz wrote:
Bigger problem, does that mean the 8th of July or the 7th of August ? Yes, I picked "08/07/2019" for that reason. It's in US style appropriate for Puerto Rico, whereas the same date in typical German style would be "07.08.2019". If I were in charge of printing those dates, I'd use the international style "2019-08-07" to help avoid this ambiguity. The FDA regulations for drug expiration dating do not specify a date format, nor do they specify a resolution or whether UTC or local time should be used. A label can say just "Expires 2021" and apparently that's good enough. (It'd probably be pushing the envelope for a label to say "Expires in the third millennium".) See: https://www.ecfr.gov/cgi-bin/text-idx?node=se21.4.211_1137 for anything that is years away from the manufaacture date is anyone (FDA included) really asking anyone to believe that one day here or there is going to make a difference, especially as environmental conditions are most likely not well controlled. The hour (or two) of time variation for summer time is never going to be relevant. Absolutely. In practice expiration dates are a big deal for only a few drugs: tetracycline, liquid antibiotics, insulin, nitroglycerin, epi pens, thyroid, blood thinners, some eye drops, and a few others. For most drugs, you can use stuff that is several years expired and you'll be fine. (If you're not expert in a drug, of course it's best to play safe and throw out expired drugs.) For more, see: Allen M. The myth of drug expiration dates. Pro Publica. 2017-07-18 05:00 -05. https://www.propublica.org/article/the-myth-of-drug-expiration-dates
Device expiration date is specified as ISO: https://www.ecfr.gov/cgi-bin/retrieveECFR?n=se21.8.801_118 "Title 21 → Chapter I → Subchapter H → Part 801 → Subpart A → §801.18 Title 21: Food and Drugs PART 801—LABELING Subpart A—General Labeling Provisions §801.18 Format of dates provided on a medical device label. ... (a) In general. Whenever the label of a medical device includes a printed expiration date, date of manufacture, or any other date intended to be brought to the attention of the user of the device, the date must be presented in the following format: The year, using four digits; followed by the month, using two digits; followed by the day, using two digits; each separated by hyphens. For example, January 2, 2014, must be presented as 2014-01-02. ... [78 FR 58818, Sept. 24, 2013]" -- Take care. Thanks, Brian Inglis, Calgary, Alberta, Canada This email may be disturbing to some readers as it contains too much technical detail. Reader discretion is advised.
Vigorous agreement here, but I would like to emphasize a little point... Paul Eggert <eggert@cs.ucla.edu> wrote:
For example, the software might be told something like "This bond payment is due at 17:00 Morocco time on January 28, 2029", one program uses tzdb 2018i and another program uses tzdb 2018g to convert that timestamp to UTC internally, and their internal UTC timestamps disagree. Here the real bug is assuming that an operation like "convert from Morocco time to UTC" is a portable and repeatable operation, which it certainly is not for future timestamps, and sometimes not even for past timestamps.
Robert Elz <kre@munnari.OZ.AU> wrote:
If your mortgage were in New York (probably a huge one, and 20 years might not be enough...) then the end date time of that 20 year mortgage should be recorded as "5:00 p.m. on the January 29, 2039, in New York City".
If your mortgage is in Hong Kong (might need to be even longer than for New York!) the end date time should be recorded as "17:00 on 29th of January, 2039, in Hong Kong".
Under no circumstances (other than specific agreement by the parties) should it be recorded as "2039-01-29 21:00:00 UTC" or "2030-01-29 09:00:00 UTC" (if I did the conversions in my head correctly) - that would always be wrong
There's a common error (which is embedded in the iCalendar specification, so it's s difficult error to avoid) of recording future times as time + timezone name, instead of time + place. Of course, the mapping from place -> timezone name is not stable... Tony. -- f.anthony.n.finch <dot@dotat.at> http://dotat.at/ Shannon, Southwest Rockall: Northwesterly 6 to gale 8, decreasing 5 later. High, becoming very rough later. Wintry showers. Good, occasionally poor.
On 1/29/19 8:43 AM, Tony Finch wrote:
There's a common error (which is embedded in the iCalendar specification, so it's s difficult error to avoid) of recording future times as time + timezone name, instead of time + place. Of course, the mapping from place -> timezone name is not stable... One thing to note is that the mapping from place to time zone is also not necessarily unique, and the mapping from place to relevant political/cultural entity is /also/ not necessarily stable.
I think there's actually no right way to do this, at least not at the moment, because the whole thing is built on shifting sands and we're not able to maintain a registry of all possible adjudicating bodies for timekeeping that will ever exist. One thing that//could help with this problem and that /might/ be scoped well for this project to do, though, would be to ship a machine-readable data structure that indicates something about the history of time zone boundaries. So one example of this could be that Asia/Qyzylorda split into Asia/Qyzylorda and Asia/Qostanay recently. What this means is that anything that started using Asia/Qyzylorda before the split is now ambiguous and may need to be manually reallocated to Asia/Qostanay. Having a way to programmatically detect when these ambiguities arise might help things. Maybe this already exists (outside of the changelogs) and I missed it, though.
On Tue, 29 Jan 2019 at 15:28, Paul Ganssle <paul@ganssle.io> wrote:
One thing that could help with this problem and that *might* be scoped well for this project to do, though, would be to ship a machine-readable data structure that indicates something about the history of time zone boundaries.
Boundaries and specific geometries, no. There are all sorts of problems with trying to undertake such an endeavor, which have been well-discussed and largely boil down to territorial disputes. There are others who make reasonable attempts, but that's been pretty explicitly regarded as out of tz's scope for some time and is mostly why we limit ourselves to listing "representative" location names.
Having a way to programmatically detect when these ambiguities arise might help things.
That said, all you *really* need is to effectively say something like "Asia/Qostanay was forked off of Asia/Qyzylorda in tzdata 2018h", so one would know, if one was using Asia/Qyzylorda prior to taking version 2018h or later, that one should manually review one's data. This is probably a good idea, and fits the scope of the existing project a bit better, and there's no need to contend with actual political boundaries. The main problem there is that no one's really been keeping that sort of metadata thusfar. -- Tim Parenti
On Jan 29, 2019, at 5:27 PM, Paul Ganssle <paul@ganssle.io> wrote:
On 1/29/19 8:43 AM, Tony Finch wrote:
There's a common error (which is embedded in the iCalendar specification, so it's s difficult error to avoid) of recording future times as time + timezone name, instead of time + place. Of course, the mapping from place -> timezone name is not stable... One thing to note is that the mapping from place to time zone is also not necessarily unique, and the mapping from place to relevant political/cultural entity is also not necessarily stable. I think there's actually no right way to do this, at least not at the moment, because the whole thing is built on shifting sands and we're not able to maintain a registry of all possible adjudicating bodies for timekeeping that will ever exist.
One thing that could help with this problem and that might be scoped well for this project to do, though, would be to ship a machine-readable data structure that indicates something about the history of time zone boundaries. So one example of this could be that Asia/Qyzylorda split into Asia/Qyzylorda and Asia/Qostanay recently. What this means is that anything that started using Asia/Qyzylorda before the split is now ambiguous and may need to be manually reallocated to Asia/Qostanay. Having a way to programmatically detect when these ambiguities arise might help things.
Maybe this already exists (outside of the changelogs) and I missed it, though.
There is no unique solution to this problem because it highly depends on application rules/local laws/user preferences/whatever. On a personal note, I’m against the idea of hiding the issues to our users (saving local place instead of timezone is -I think- a way of hiding or automate this problem). Instead, I think we need to exposed it in “a better way”, and let some human decide what is right (because the rules/laws/whatever are really hard to automate). I really like the idea of saving time + timezone (+ tzdb version somewhere!). And make *your users choose/switch* their current timezone, and if there are issues while switching, tell them what are the issues. If a new tzdb is released, tell your users what is affected. If a place has a new timezone, tell your users about this, and if they switch to another timezone, again, tell them what is affected. Maybe your user is not your “final user”, it could be your sysadmin or whatever. But somewhere in the process of updating tzdb there should be a human that press a button saying “I’m ok with this changes” or “oh no, we need to recalculate all mortgages!”. I wrote a tool that helps calculate what is changed whenever you change from timezone A to B and/or tzdb release X to Y (https://a0.github.io/a0-tzmigration/ <https://a0.github.io/a0-tzmigration/>). It is far from perfect, but I think it may help to build systems as I described here. — Aldrin.
participants (12)
-
Aldrin Martoq Ahumada -
Brian Inglis -
Fred Gleason -
Guy Harris -
Joachim Damm -
Paul Eggert -
Paul Ganssle -
Phake Nick -
Robert Elz -
Sergiusz Wolicki -
Tim Parenti -
Tony Finch