Search code examples
jdbcclojurefirebirdjaybird

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

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"})
Solution

  • 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:

    1. 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.
    2. Use getTimestamp(int index, Calendar calendar) with a Calendar that has the appropriate time zone configured
    3. 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.
    4. 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.