Should I store the timezone separately from the timestamp for Postgres and JDBC?

Question

It seems (and maybe I'm wrong) that if you want to preserve the timezone of when something happened with JDBC and Postgres you need to store the timezone separately from the timestamp.

That is I would prefer to give my ORM/JDBC/JPA a Java Calendar (or Joda DataTime) with say timezone America/New_York to a Postgres timestampz field. AND I would expect on retrieval regardless of the Servers timezone (or defaulting to UTC) to give me back a Calendar with timezone America/New_York. But just looking at most JDBC code (and things that depend on it that doesn't happen).

Is this correct?

This seems ridiculous that I would need to store the tz in another field when postgres supports it.

Thus it seems like the only two options are:

Select the timestampz Postgres column as a java.util.String and parse it.
Store the timezone as a separate field.

Option number one and two one would require some sort of conversion interceptors for my SQL mapping / ORM libraries.

What is the best solution for JDBC ?
What is the best solution for JPA (if different than JDBC)?

When you store a timestamptz it's converted to UTC for storage in the DB. When retrieved, it's converted to the client's current timezone, not the timezone it was originally in. It's a point in time, basically. — Craig Ringer

Craig Ringer Craig Ringer · Accepted Answer · 2012-09-09T11:40:01

When you store a timestamp with time zone (timestamptz) it's converted to UTC for storage in the DB. When retrieved, it's converted to the client's current timezone, not the timezone it was originally in. It's a point in time, basically.

There is also timestamp without time zone (timestamp). This is not subject to conversion, but does not carry a timestamp with it. If you store a timestamp with your client time zone set to UTC, then retrieve it when the client time zone is '+08:00', you get the same value. That's half what you want, in that it preserves the raw time value.

The names and behaviours are awful and confusing, but set by the SQL standard.

You must store the time zone separately if you wish to record a point in time at a particular time zone. I'd recommend storing it as an INTERVAL with a CHECK constraint limiting it to be colname BETWEEN INTERVAL '-12' HOUR + INTERVAL '1' SECOND AND INTERVAL '12' HOUR. That definition rejects -12:00 and accepts +12:00; I'm not totally sure that's right, so check.

You could either store the timestamp of local time at that time zone (what I'd probably do), or store the timestamptz of the UTC time when the event occurred plus an offset that lets you convert it to local time.

Either will work fine for JDBC. For JPA, it'll depend on how well your provider understands and maps interval types. Ideally you want a transient generated field in your entity that reconstructs the Calendar instance you want using the timestamp and interval stored in the database.

Should I store the timezone separately from the timestamp for Postgres and JDBC?

2 Answers