What timezone information does PostgreSQL store?

Question

The PostgreSQL documentation is fairly thorough and useful:

https://www.postgresql.org/docs/9.2/datatype-datetime.html#DATATYPE-TIMEZONES

but seems to overlook clarity on a rather useful point where clarity might be warranted and help. Having read the documentation and various related stackoverflow questions and responses, I am left suspecting that the following is true:

The PostgreSQL datatype timestamp with timezone stores a date and time and a utcoffset (+ve being east of Greenwich)

I would further infer and suspect it is true that:

The PostgreSQL datatype timestamp with timezone stores a date and time and a utcoffset (+ve being east of Greenwich) to minute resolution.

My question relates to these inferences. Are they correct, and if so what evidence can be forwarded to confirm them, and if not what evidence can be forwarded to the contrary.

The main reason this is interesting is because of course if true, then PostgreSQL which accepts timezones by name or abbreviation as stored in the table pg_timezone_names only stores the UTC offset and thereby loses DST information.

Meaning, to make the actual time zone name (as defined in the table pg_timezone_names) available to a reader in the future it must be explicitly stored alongside the timestamp with timezone in a column beside it.

The main reason this interests me right now is that I had in mind what I felt was a reasonably clever way of rendering times that can record the time of an event anywhere on earth. Namely if the recorded time is in the users current timezone, then report it as a naive date/time (no timezone info), and only if it is in a timezone different to the readers, report the timezone information (and even then, the timezone name may be more user friendly than the UTC offset).

And it looks like I will be obliged to store timezone name beside my event times (and any other timezone aware date/time's I store) if I wish to implement such contextually sensitive rendering on a website.

But I feel ill at ease making such a commitment on the basis of inference, and not knowledge and would like some evidence supporting or contradicting these inferences.

September

You can always retrieve those timestamps using ... at time zone ... and provide the user's time zone – Anthology

Answer 1

Both your assumptions are wrong:

PostgreSQL stores a timestamp with time zone as 8-byte integer that contains the offset from 2000-01-01 00:00:00 UTC in microseconds.

So it neither stores the time zone, nor is the precision 1 minute.

Upon conversion to a string, the timestamp is formatted according to the current setting of the timezone parameter.

So if you have to store the time zone separately if you need to remember it and use the AT TIME ZONE expression to convert the timestamp to the proper time zone.

You ask for documentation references. Part of that is here:

/*
 * Timestamp represents absolute time.
[...]
 * Timestamps, as well as the h/m/s fields of intervals, are stored as
 * int64 values with units of microseconds.  (Once upon a time they were
 * double values with units of seconds.)

In the same file, you find

/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE        2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE    2451545 /* == date2j(2000, 1, 1) */

Colbycolbye

Thanks kindly Laurenz. But it leaves me with two questions alas: 1) Where is this documented (i.e. where is the evidence, and how do you know this, or how could I have known it without posting a question). 2) What you suggest is suspicious I admit. How so? Because the data type is named timestamp with timezone which apparently is not, if I trust your description it is better named and thought of as UTC timestamp. What I am really keen on is some real documentation in this space. But I fear you are right in the sense that to do what I need I need to store timezones separately. – September

OK, I found a further clue here that definitely documents the 8 byte storage identity between the with and without timezone timestamps: [javatpoint.com/postgresql-datatypes]. That's part/most of the way there. And here I found a great article [tapoueh.org/blog/2018/04/… and that may be enough documentation. – September

I have added a link to the documentation. – Colbycolbye