On 03/06/2020 13:31, Robert Elz wrote:
The problem there is having discarded the original data instead of retaining it. Always retain the original source data. Then by all means, when computing, convert timestamps from their various local values to UTC so they can be more easily correctly ordered (or whatever) but use those converted values only for transient computations. Store the original. Always.
To some extent I totally agree with you, but unfortunately many systems simply assume that there is never a problem with the TZ data so they don't need to store both values. In fact they simply assume that ALL they need to store is the offset, so have no idea that in six months time the offset may be different. Firebird is just 'adding timezone datatypes' and simply ignore anything pre 1970 so have no way to store the initial seconds offsets even. Just go with what works for most people and ignore the rest? SQL standards are just a mess when it comes to timezone data types and simply storing some tz identifier is not the whole story! I came into this 20 years ago now while working with a data archive which has now been simply dumped because we had no idea what rules were used to produce the normalised data. Nowadays yes it does make sense to store both an original time and a normalised time, along with a location, and a record of which version of rules was used to do the normalization. Add to that a flag that indicates if the UTC time is fixed! My point is that for a miniscule number of users a short term change in TZ data may affect their plans yet much of the time it is judged to difficult to bother about? Now with more virtual international meetings, the potential of sessions moving an hour is not zero and being able to easily identify that the recorded normalized time has now changed at a very minimum flags that you need to check if the local time now needs to change since the UTC time is fixed by the event calendar. In the past I have had cases where international medical meetings have been disrupted over ramadan when sessions started at local time ignoring the fact that is was now an hour adrift from the master timetable. The website may have been updated at short notice, but how long do browsers hold 'old' copies but in most cases the 'local' staff did not even think about their overseas venues :( For historic data then the gold standard is two timestamps, location information and the rule set version ... along with a tolerance value, but the published time is UTC ... -- Lester Caine - G8HFL ----------------------------- Contact - https://lsces.uk/wiki/Contact L.S.Caine Electronic Services - https://lsces.uk Model Engineers Digital Workshop - https://medw.uk Rainbow Digital Media - https://rainbowdigitalmedia.uk