Re:SQL for Distributed Systems
by funcDropShadow on Thursday October 14, 2021 @03:16AM
(#28861397)
Unfortunately, my colleagues and me had the displeasure to look at that distinction between timestamp and timestamptz in Postgres very deeply.
All that is written above is correct from my understanding, but it is not the complete picture. Even in the case of a column of type `TIMESTAMP WITHOUT TIME ZONE`, the Postgresql JDBC driver may convert time to UTC if your session time zone is not UTC. I write "may" because it depends on whether the binary protocol or the text base protocol is used and that depends on whether prepared statements are actually used between the Postgres client library and the server.
We had the requirement to store dates and times coming it without changing them at all cost, but still using the space efficient epoch format. And we didn't know the time zone of the time when we had to store it.
Re:SQL for Distributed Systems
by colanderman on Thursday October 14, 2021 @09:00AM
(#28863656)
Ugh, sounds like a bug in the JDBC driver. I've found that type translation in some drivers is... incomplete let's say. I seem to recall working around similar issues in psycopg2/3(?).
The PostgreSQL server itself though I've found to be very consistent handling these types.
Re:SQL for Distributed Systems
by funcDropShadow on Thursday October 14, 2021 @04:13PM
(#28869393)
Yes, PostgreSQL seems always to be consistent and very well documented.
The types are so named because values of type "TIMESTAMP WITH TIME ZONE" are always displayed with a time zone (corresponding to the system's time zone) -- and the numeric value adjusted accordingly -- so the time they represent is unambiguous. Values of type "TIMESTAMP WITHOUT TIME ZONE" are displayed as-is without a time zone.
The types behave differently when used with the "AT TIME ZONE" operator as well, which converts between the two. When presented with a "TIMESTAMP WITH TIME ZONE" value, "AT TIME ZONE" returns a "TIMESTAMP WITHOUT TIME ZONE" value which numerically is equal to the time in the specified time zone. When presented with a "TIMESTAMP WITHOUT TIME ZONE" value, "AT TIME ZONE" interprets the given time as relative to the indicated time zone, and returns a "TIMESTAMP WITH TIME ZONE" value representing that absolute point in time.
Consequently, in cases where you would use UTC in other systems, you should use "TIMESTAMP WITH TIME ZONE" in PostgreSQL. In cases where you would use a local time in other systems, you should use "TIMESTAMP WITHOUT TIME ZONE" along with a "text" value naming the local time zone when appropriate.