date time result from sql database is not same in clojure jdbc

Question

I have problem with timestamp in database(firebird) with jdbc

data in database

timestamp 1994-10-12T00:00:00.000000000-00:00

I test with python and result is same in database but when I use jdbc(clojure)

result is 1994-10-11T17:00:00.000000000-00:00

I think it depend on timezone(I am in GMT+7)

How to fix it?

Thank you.

this code

(ns test.core
  (:require [clojure.java.jdbc :as jdbc]))

        (def firebird-setting {:description "Firebird Database"
                           :classname   "org.firebirdsql.jdbc.FBDriver"
                           :subprotocol "firebirdsql"
                           :subname     "//localhost:3051//firebird/data/test.fdb"
                           :user        "user"
                           :password    "pass"})

    (jdbc/query firebird-setting
                "select ts from TestTB")

and result

({:ts #inst "1994-10-11T17:00:00.000000000-00:00"})

Can you please post the Clojure code you use to retrieve the timestamp? — Aleph Aleph, Jul 18 '18 at 15:56
Please post the code used, and be aware that JDBC has peculiar rules about how to handle retrieval of `java.sql.Timestamp`, see https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific/14070771#14070771 — Mark Rotteveel, Jul 18 '18 at 16:02
That doesn't show how you retrieve the timestamp value. Are you relying on some implicit conversions here? Because that print out is - for example - not the default `toString()` of `java.sql.Timestamp`, which means that more is going on below the surface. — Mark Rotteveel, Jul 18 '18 at 16:11
that all of my code and result come from repl, not print it out. For now I set timezone in jvm option that work but I dont want to do that. — Veerapat Boonvanich, Jul 18 '18 at 16:20

Mark Rotteveel · Accepted Answer · 2018-07-18T19:51:09.167

The problem is that JDBC requires that timestamps (without time zone information) are retrieved as if the value is in the default (current) JVM time zone. See also Is java.sql.Timestamp timezone specific?

This means that if the value stored in your database is 1994-10-12 00:00:00, and your JVM time zone is GMT+7, then the time will be retrieved as 1994-10-12 00:00:00+07:00, which is the same as 1994-10-11T17:00:00+00:00.

The normal solutions to this are:

Change the default JVM time zone (or even the locale config of your machine), so it is GMT. Consider this option carefully, as this can have other effects on your application.
Use getTimestamp(int index, Calendar calendar) with a Calendar that has the appropriate time zone configured
Use getObject(int index, Class clazz) with LocalDateTime.class as parameter (if you use Jaybird 3.0.x). This will retrieve the value as a java.time.LocalDateTime which is timezone-less, and therefor has none of these issues.
Use the knowledge that the time is retrieved in the default JVM time zone and convert it appropriately (eg by rendering it with a date format that takes the right zone into account).

I don't know Clojure, so unfortunately I can't offer a Clojure specific answer.

I wonder if that could make sense to add timezone into jaybird connection options for that conversion, or would it violate jdbc specs? — Arioch 'The, Jul 19 '18 at 07:23
@Arioch'The Technically that would violate the JDBC spec, but it is a possibility. I might consider introducing something like that when I overhaul the implementation to support the `time(stamp) with time zone` data types and session time zones config. I'm not sure about that yet. — Mark Rotteveel, Jul 19 '18 at 07:36
Then, if you would chose to implement it, perhaps JayBird to issue a warning that it enters into deliberately requested JDBC-deviating mode. OTOH there were not many people reading warnings about NONE connection charset — Arioch 'The, Jul 19 '18 at 13:30

date time result from sql database is not same in clojure jdbc

1 Answers1