timestamp with time zone - confused by documentation?

[f-rich]

From https://www.npgsql.org/doc/types/datetime.html

A common mistake is for users to think that the PostgreSQL timestamp with time zone type stores the timezone in the database. This is not the case: only a UTC timestamp is stored. There is no single PostgreSQL type that stores both a date/time and a timezone, similar to .NET DateTimeOffset.

But if I store some values with various offsets in a timestamp with time zone column I can see that the offset is preserved i.e. the offset is stored in the database?

The PostgreSQL documentation does say:

All timezone-aware dates and times are stored internally in UTC

But I take that to mean that internally Postgres is also storing the offset and then reapplying it on the way back out.

Simple repro:

1. Create a table with a timestamp with time zone column
2. Insert a couple of rows with different offsets
3. Select those rows
4. Note that the different offsets are preserved

What am I missing?

[roji]

But if I store some values with various offsets in a timestamp with time zone column I can see that the offset is preserved i.e. the offset is stored in the database?

That's not correct, and is a common (and very understandable) mistake. The timezone offset isn't saved in the database; when selecting the timestamptz out (in text mode), PG only formats it as a local timezone, based on whatever value the TimeZone parameter happens to have. Try changing your TimeZone parameter (SET TimeZone='Europe/Berlin') and selecting the timestamptz out again, and you'll see a different value.

From the PG docs:

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.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4).

[f-rich]

Thanks for the reply.

I've tried setting different zones for the session and I only ever get the same results; have tried Europe/Berlin, UTC and America/Los_Angeles.

It must be storing something in the database?

insert into timezonetest(test) values('2023-01-01 09:00:00+06:00')
insert into timezonetest(test) values('2023-01-01 09:00:00+09:00')

select *, current_setting('TIMEZONE')
from timezonetest

I'm clearly missing something! :-)

[f-rich]

Damn.

In my haste to knock-up a quick test I've chosen "time with time zone" by mistake - blame pgAdmin/fat fingers.

[f-rich]

Oh the shame!

I'll leave this here for others as a warning :-D

[roji]

Don't feel bad 🤣 This is a super confusing aspect of date/time handling and many fall into this trap!