Max Nikulin <maniku...@gmail.com> writes: > On 14/01/2023 16:32, Tim Cross wrote: >> If org was to add TZ capabilities to timestamps, the underlying format >> would have to be UTC. > ... >> can change based on various criteria, including political whims >> (e.g. Australia eastern DST transition date was changed in 2000 because >> Sydney hosted the Olympics that year). > > Due to this particular reason storage format for significant fraction of > future timestamps > (but not always) must not be in UTC > > 2024-01-14 Sun 21:14:58 ACS (+0930, Australia/Darwin) > > is not the same as > > 2024-01-14 Sun 11:45:58 UTC (+0000, Z) > > despite currently it is. >
I"m sorry, but I don't follow. The UTC time is the only time whihc is not affected by daylight savings transitions, so is the only stable metric. All the others are relative to that time but can change based on things like changes in DST transition dates/times. When DST transitions occur, time is lost/gained wrt the local time, but real time and UTC time do not change. So when DST starts here at 2am on the llth it is 5pm on the 10th UTC. At 2am locally, we jump to 3am, losing an hour, but the UTC time is still 5pm. The meeting I had scheduled for 10am with friends form the US was at 0:00 UTC. It is now at 11am local time, but still at 0:00 UTC, however, I will likely fall asleep in it because instead of my normal 7 hours sleep, I only got six despite going to be and getting up at the same time. > Depending on use case the same particular fields of timestamp may be > authoritative or > derived for user convenience from other data. > > UNIX timestamps in seconds in UTC timezone almost unavoidable will be used > underneath, but > operations like "start of next day" require non-trivial computations to find > if time zone > offset changes in between. Yes, but if the underlying representation is UTC, then all you have to do is apply local TZ data to get the local time. THis is how postgres works. You just hav eto ensure the TZ data you get is the TZ data for that date in that locale.
