jOOQ Timestamp being stored with local Timezone offset

Question

PostgreSQL 9.3 / postgresql-9.3-1100-jdbc41.jar

I have a table with a column of type timestamp without time zone, this generates my Object with the applicable java.util.Timestamp property.

What I'm seeing, during insert, is jOOQ's binding process converting a java.util.Timestamp into a date with local timezone offset.

eg for a unix timestamp 1421109419 (13 Jan 2015 00:36:59 GMT) the property is set with new Timestamp(1421109419 * 1000).

from the jOOQ logger I see:

2015-01-13 14:14:31,482 DEBUG [http-bio-8002-exec-4] org.jooq.tools.LoggerListener#debug:255 - -> with bind values      : insert into "foo"."bar" ("start_date") values (timestamp '2015-01-13 13:36:59.0') returning "foo"."bar"."id"

2015-01-13 14:14:31,483 TRACE [http-bio-8002-exec-4] org.jooq.impl.DefaultBinding#trace:179 - Binding variable 3       : 2015-01-13 13:36:59.0 (class java.sql.Timestamp)

and sure enough in the record is the value "2015-01-13 13:36:59".

The software is running on a machine in NZDT which explains the +13 offset.

Given the time is being supplied in a TimeZone agnostic container (Timestamp) I would have expected that to be honoured when creating the insert statement.

How can I have jOOQ create timestamps NOT in local time?

Answer 1

Unfortunately you have a few things working against you:

1. The PostgreSQL JDBC driver sets the timezone to your JVM timezone in the Postgres session. So even if your Database Server is running in UTC a TIMESTAMP field will be inserted using the time zone of your JVM. When you insert or query data the database server will always use the JVM time zone.
2. You are using TIMESTAMP instead of TIMESTAMPTZ. The description of these types do not reflect their actually usage. TIMESTAMPTZ actually means time zone agnostic. Whatever value you insert it will be adjusted to UTC using the session timezone.

Because of these two issues, if you have two different JVMs -- one using Los Angeles time and the other using New York time -- whenever you write a TIMESTAMP with one JVM it will be a different "UTC time" in the other JVM. TIMESTAMP takes the adjusted value and just uses it as given. If you change your TIMESTAMP columns to be TIMESTAMPTZ then the same time in both JVMs will always be the same UTC time.

If you look at the Postgres JDBC Driver's ConnectionFactoryImpl#openConnectionImp you can see where it sets your local JVM's time zone as the time zone for the database server's session zone.

So the only sane way to deal with this is to only ever use TIMESTAMPTZ instead of TIMESTAMP. Here's some more information on this:

PostgreSQL/JDBC and TIMESTAMP vs. TIMESTAMPTZ

http://justatheory.com/computers/databases/postgresql/use-timestamptz.html