5 ms·

Neither "TIMESTAMP WITH TIME ZONE" nor "TIMESTAMP WITHOUT TIME ZONE" stores a time zone. The difference is in the meaning of the value: a "TIMESTAMP WITH TIME

Neither "TIMESTAMP WITH TIME ZONE" nor "TIMESTAMP WITHOUT TIME ZONE" stores a time zone. The difference is in the meaning of the value: a "TIMESTAMP WITH TIME ZONE" value represents an absolute time in history; "TIMESTAMP WITHOUT TIME ZONE" represents a time relative to some unspecified time zone. 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.

funcDropShadow 5y ago
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.
- colanderman 5y ago
  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.
  - funcDropShadow 5y ago
    Yes, PostgreSQL seems always to be consistent and very well documented.

funcDropShadow 5y ago

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.

colanderman 5y ago
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.
- funcDropShadow 5y ago
  Yes, PostgreSQL seems always to be consistent and very well documented.