3

I'm in a GMT+1 timezone, and I have a UTC timestamp (11:12:56) in a long variable.

long ts = 1487157176450;

I initialize a java.sql.Timestamp from this timestamp.

Timestamp timestamp = new Timestamp(ts);

Then, I insert this timestamp into a PostgreSQL (v9.4) database using jOOQ.

create.insertInto(TABLE,
    TABLE.NAME, TABLE.TS)
  .values("Foo bar", timestamp);

However, when I've logged the actual SQL commands executed, I saw this:

< 2017-02-15 10:50:37.326 CET >LOG:  execute <unnamed>: insert into "database"."table" ("name", "ts") values ($1, cast($2 as timestamp))
< 2017-02-15 10:50:37.326 CET >DETAIL:  parameters: $1 = 'Foo bar', $2 = '2017-02-15 12:12:56.450'

I'm not entirely sure where did the timestamp get turned into a local one, but unless pgAdmin lies, I actually do store GMT+1 timestamps in the database instead of the UTC I intended.

Now as I am not using JDBC, the answer to the linked question isn't helpful. How do I make jOOQ store the right timestamp?

Tomáš M.
  • 752
  • 7
  • 24
  • you can set time zone to UTC for your client, or use explicit cast `cast($2 as timestamptz)` I suppose – Vao Tsun Feb 15 '17 at 11:36
  • The JDBC specification requires that a timestamp is sent in the JVM default timezone, unless 1) explicitly specified by passing a `Calendar` with the right timezone, 2) inserting into a column with a specific time zone, 3) using a `java.time.OffsetDateTime` – Mark Rotteveel Feb 15 '17 at 11:39
  • I noticed that you didn't accept any answer yet. Please consider doing so at some point. – GhostCat Jun 27 '18 at 04:02

1 Answers1

1

The Timestamp class itself knows nothing about time zones.

In other words: it assumes that the incoming value is UTC.

But when the Timestamp is further processed (and nothing is set specifically), the value that is "retrieved" from the Timestamp object and pushed into the database is taking your current locale into account.

You can look here for some further thoughts. The answer by Mikael Valot gives some examples how you can retrieve long values to be passed to the TimeStamp constructor in regards of timezones.

Community
  • 1
  • 1
GhostCat
  • 137,827
  • 25
  • 176
  • 248
  • 1
    Right, so how do I tell jOOQ (assuming that's the culprit) not to translate the Timestamp into my current locale? – Tomáš M. Feb 15 '17 at 11:40
  • @TomášM. See also https://blog.jooq.org/tag/time-zones/ and http://stackoverflow.com/questions/27913735/jooq-timestamp-being-stored-with-local-timezone-offset – Mark Rotteveel Feb 15 '17 at 11:43
  • 1
    @TomášM.: This isn't related to jOOQ (which just passes on the `Timestamp` to JDBC), but a JDBC / PostgreSQL problem. You probably want to use `timestamp with time zone` as a PostgreSQL data type, and apply the correct data type binding in jOOQ, to map this to `java.time.OffsetDateTime` – Lukas Eder Feb 17 '17 at 09:01
  • Thanks Lukas, that indeed fixed the issue. – Tomáš M. Feb 17 '17 at 13:51