PostgreSQL/JDBC and TIMESTAMP vs. TIMESTAMPTZ

Question

I've been going through a lot of pain dealing with Timestamps lately with JPA. I have found that a lot of my issues have been cleared up by using TIMESTAMPTZ for my fields instead of TIMESTAMP. My server is in UTC while my JVM is in PST. It seems almost impossible with JPA to normalize on UTC values in the database when using TIMESTAMP WITHOUT TIMEZONE.

For me I use these fields for stuff like "when was the user created", "when did they last use their device", "when was the last time they got an alert", etc. These are typically events so they are instance in time sorts of values. And because they will now by TIMESTAMPTZ I can always query them for a particular zone if I don't want them UTC.

So my question is, for a Java/JPA/PostgreSQL server, when WOULD I want to use TIMESTAMP over TIMESTAMPTZ? What are the use cases for it? Right now I have a hard time seeing why I'd ever want to use TIMESTAMP and because of that I'm concerned that I'm not grasping its value.

Answer 1

With the Java 8 date & time API I wouldn't blindly jump into a timestamptz camp.

If you map timestamp <=> LocalDateTime you always get the same value regardless default Java application timezone. Regardless how many calls TimeZone.setDefault(TimeZone.getTimeZone("TZ")) mixing different TZ you put in between SELECT/INSERT you will get the same LocalDateTime in Java at any time and date/time components will be the same as in Postgresql TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS').

If you map timestamptz <=> LocalDateTime Postgresql JDBC driver (supporting JDBC 4.2 spec) converts LocalDateTime to UTC using default Java timezone when saving value to DB. If you save it in one default TZ and read in another you get different "local" results.

Airplane departure time is local to an airport. If you don't need to compare departure time between different cities timestamptz & UTC doesn't make sense, you just print exact city local time in a ticket. With timestamp it is possible to keep date/time as is, avoiding double TZ correction due to Java app default TZ + city specific TZ (business logic).

timestamptz is useful when you heavily convert TZ in SQL. With only timestamp you write:

date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE x.timezone)
  AT TIME ZONE x.timezone AT TIME ZONE 'UTC'

while with timestamptz there is no need to mention that time is in UTC (if you follow such convention, probably you should xD):

date_trunc('day', x.datecol AT TIME ZONE x.timezone)
  AT TIME ZONE x.timezone

Operator AT TIME ZONE is overloaded:

• timestamp AT TIME ZONE 'X' => timestamptz
• timestamptz AT TIME ZONE 'X' => timestamp

Postgresql JDBC + Java 8 date&time API spec.

Answer 2

Generally use TIMESTAMPTZ

Here's advice from David E. Wheeler, a Postgres expert, in a blog post whose title says it all:
Always Use TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)

If you are tracking actual moments, specific points on the timeline, use TIMESTAMP WITH TIME ZONE.

One Exception: Partitioning

Wheeler’s sole exception is when partitioning on timestamps, because of technical limitations. A rare exception for most of us.

For information about partitioning, see doc and see the Wiki.

Misnomer

The data types names timestamp with time zone and timestamp without time zone are misnomers. In both cases the date-time value is stored in UTC (no time zone offset). Read that previous sentence again. UTC, always. The "with time zone" phrase means "with attention paid to time zone", not "store the time zone alongside this value". The difference between the types is whether any time zone should be applied either during storage (INSERT or UPDATE) or retrieval (SELECT query). (This behavior is described for Postgres -- Other databases vary widely in this regard.)

More precisely, one should say that TIMESTAMP WITHOUT TIME ZONE stores date-time values with no time zone. But without any time frame reference, anyone looking at that data would have to assume (hope, pray?) that the values are UTC. But again, moot as you should almost never use this type.

Read the doc carefully, and experiment a bit to clarify your understanding.

Unzoned

If you want to store the general idea of a possible time rather than a specific moment, use the other type, TIMESTAMP WITHOUT TIME ZONE.

For example, Christmas starts this year at the first moment of December 25th, 2017. That would be 2017-12-25T 00:00:00 with no indicator of time zone nor offset-from-UTC. This value is only a vague idea about possible moments. It has no meaning until we apply a time zone (or offset). So we store this using TIMESTAMP WITHOUT TIME ZONE.

The elves staffing Santa’s Special Events Logistics Department apply the time zones as part of their planning process. The earliest time zone is currently Pacific/Kiribati, 14 hours ahead of UTC. The elves schedule Santa’s first arrival there. The elves schedule a flight plan taking the reindeer on to other time zones where midnight comes shortly after, such as Pacific/Auckland. They continue going westward as each zone’s midnight arrives. Hours later in Asia/Kolkata, still later in Europe/Paris, still more hours later in America/Montreal and so on.

Each of these specific delivery moments would be recorded by the elves using WITH TIME ZONE, while that general idea of Christmas would by stored as WITHOUT TIME ZONE.

Another use in business apps for WITHOUT TIME ZONE is scheduling appointments farther out than several weeks. Politicians around the world have an inexplicable predilection for messing with the clock and redefining time zone rules. They join Daylight Saving Time (DST), leave DST, start DST on a different date, or end DST on a different date, or shift their clocks by 15 minutes or half-hour. All of these have been done in last several years by Turkey, United States, Russia, Venezuela, and others.

The politicians often make these changes with little forewarning. So if you are scheduling a dental appointment for six months out at 13:00, that should probably be stored as TIMESTAMP WITHOUT TIME ZONE or otherwise the politicians may effectively be changing you appointment to noon, or 2 PM, or 13:30.

Answer 3

Update for the answers above: partitioning is no longer an exceptional case in PG11 thanks to pruning.

https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITION-PRUNING

Personally successfully tested queries against PG11 AWS RDS. Also the official PG wiki states the use of timestamp without timezone is a bad idea:

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29_to_store_UTC_times

Answer 4

You could use it to represent what Joda-Time and the new Java 8 time APIs call a LocalDateTime. A LocalDateTime doesn't represent a precise point on the timeline. It's just a set of fields, from year to nanoseconds. It is "a description of the date, as used for birthdays, combined with the local time as seen on a wall clock".

You could use it to represent, for example, the fact that your precise birth date is 1975-07-19 at 6 PM. Or that, all across the world, the next new year is celebrated on 2015-01-01 at 00:00.

To represent precise moments, like the moment Armstrong walked on the moon, a timestamp with timezone is indeed more appropriate. Regardless of the timezone of the JVM and the timezone of the database, it should return you the correct moment.