Re: timestamp and timestamptz

On Wed, Apr 15, 2020 at 4:53 PM raf <raf(at)raf(dot)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.
"""

https://www.postgresql.org/docs/12/datatype-datetime.html

David J.