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
– Aleph Aleph, Commented Jul 18, 2018 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 stackoverflow.com/questions/14070572/… — Mark Rotteveel
– Mark Rotteveel, Commented Jul 18, 2018 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
– Mark Rotteveel, Commented Jul 18, 2018 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
– Veerapat Boonvanich, Commented Jul 18, 2018 at 16:20

Mark Rotteveel · Accepted Answer · 2018-07-18 19:51:09Z

5

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.

edited Jul 18, 2018 at 19:51

answered Jul 18, 2018 at 16:46

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arioch 'The Over a year ago

I wonder if that could make sense to add timezone into jaybird connection options for that conversion, or would it violate jdbc specs?

Mark Rotteveel Over a year ago

@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.

Arioch 'The Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related