>> Because the vast majority of our use cases for storing timestamps is to represent specific, unambiguous moments in time, we pretty much always want to use the timestamptz type.
> This advice is not great. One needs to specifically know their data.
I think the advice is good advice: when you want to store an unambiguous point in time, you should use the timestamptz type, as it stores the time in UTC, whatever you give it.
"To store a time that is unambiguous in a certain location
(e.g. a doctor's appointment in 4 months time) one needs
to store it as timezone without timezone and an extra
field with the locality. It makes no sense to store the
timezone, unless you also store the DST information or
unless you think you can rule out unexpected DST changes."
In postgres, timestamptz doesn't store the timezone, only the UTC value, given a timezone.
I think your example of "A doctor's appointment in 4 month's time" _isn't_ an unambiguous point in time, which is why you would need to store the wall time with the timezone information.
I can save my doctor's appointment as timestamptz in 4 months time with
However storing the Europe/London timezone doesn't help me as I can't rule out unexpected DST rule changes. If the UK parliament abolishes BST before that time, I will have to recalculate that value, otherwise I will arrive an hour early.
Hopefully if the UK Parliament abolished BST, postgres maintainers would update how the "Europe/London" timezone works to account for this change (as well as every other library that manages timezones). I would also expect there to be a transition period to ensure everyone is brought to speed and the relevant updates are made
>> Because the vast majority of our use cases for storing timestamps is to represent specific, unambiguous moments in time, we pretty much always want to use the timestamptz type. > This advice is not great. One needs to specifically know their data.
I think the advice is good advice: when you want to store an unambiguous point in time, you should use the timestamptz type, as it stores the time in UTC, whatever you give it.
"To store a time that is unambiguous in a certain location (e.g. a doctor's appointment in 4 months time) one needs to store it as timezone without timezone and an extra field with the locality. It makes no sense to store the timezone, unless you also store the DST information or unless you think you can rule out unexpected DST changes."
In postgres, timestamptz doesn't store the timezone, only the UTC value, given a timezone.
I think your example of "A doctor's appointment in 4 month's time" _isn't_ an unambiguous point in time, which is why you would need to store the wall time with the timezone information.
I can save my doctor's appointment as timestamptz in 4 months time with
# SELECT timezone('Europe/London', '2021-10-07 20:00'::timestamp);
2021-10-07 19:00:00+00
However storing the Europe/London timezone doesn't help me as I can't rule out unexpected DST rule changes. If the UK parliament abolishes BST before that time, I will have to recalculate that value, otherwise I will arrive an hour early.
SELECT timezone('Europe/London', '2021-10-07 19:00Z'::timestamptz);
2021-10-07 20:00:00 (1 row)
will change to
SELECT timezone('Europe/London', '2021-10-07 19:00Z'::timestamptz);
2021-10-07 19:00:00 (1 row)
So the wall time is used, as the point in time is ambiguous.