On Wed, Apr 15, 2020 at 4:53 PM raf <raf@raf.org> wrote:
I don't see much difference in storing a timestamptz in UTC or a timestamptz in CET. As long as the intended offset from UTC is recorded (which it is in a timestamptz) it should be fine.
I only really skimmed the entire response but this framing of how timestamptz stores results is wrong. Once you've stored a timestamptz in PostgreSQL you no longer have any knowledge of the timezone. If you truly need that you need to record that in a different field. What you do know is that PostgreSQL has a known point-in-time in UTC and can give you back the same value expressed in any other timezone according to the rules in the timezone database.
Or, as written verbatim in the documentation:
"""
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
