3 ms·
Unfortunately, my colleagues and me had the displeasure to look at that distinction between timestamp and timestamptz in Postgres very deeply. All that is writ
by 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 agoUgh, 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 agoYes, PostgreSQL seems always to be consistent and very well documented.