Fork me on GitHub
#sql
<
2023-03-27
>
lepistane21:03:05

I am having quite a weird behavior with next.jdbc. Maria db timestamp is the field. i store UTC date and when i query i get #inst that's -1h ? Why is that and how can i fix it ?

hiredman21:03:17

if you aren't storing a timezone with the date, you need to make sure the jvm's timezone is the same as the databases

hiredman21:03:43

user=> (.getID (java.util.TimeZone/getDefault))
"America/Los_Angeles"
user=>

lepistane21:03:20

how can i check if i am storing timezone in the db ?

lepistane21:03:04

Oke so mariadb timestamp is UTC always

lepistane21:03:40

My current is GMT+2

lepistane21:03:11

stored in db: 2023-01-02 13:52:00.000 i query it it becomes 2023-01-02 12:52:00.000

hiredman21:03:29

the other thing to keep in mind is regardless of the timezone of a data object, the #inst printer in clojure normalizes it to utc

hiredman21:03:14

so if you are constructing dates like (java.util.Date.) it will print in utc, but actually be in whatever timezone the jvm is running in

lepistane21:03:06

shouldn't this solve all problems?

(defn ->time-str [s]
  (time/format
   (time/with-zone (time/formatter "yyyy-MM-dd HH:mm:ss") "UTC")
   (.toInstant s)))

hiredman21:03:21

solve which problem?

lepistane21:03:50

stored in db: 2023-01-02 13:52:00.000 i query it it becomes 2023-01-02 12:52:00.000

hiredman21:03:06

the solution to this problem is to make sure the jvm process has the same timezone as the database

lepistane21:03:35

how do i do this?

hiredman21:03:37

some jdbc drivers (I dunno about maria) do timezone conversion internally on dates

lepistane21:03:15

Is there a next.jdbc option that offers 'here everything is in UTC' ?

hiredman21:03:22

there is some jvm property you can set I believe, generally on unix like systems you can set the TZ environment variable for the process, etc

hiredman21:03:31

it isn't something next.jdbc is doing

lukasz21:03:37

-Duser.timezone=UTC is the Java option that you can use

hiredman21:03:45

it is something the mariadb jdbc driver is doing

hiredman21:03:16

there are some properties mentioned on https://jira.mariadb.org/browse/CONJ-433 that you can set on your connection, but I haven't used them, and I am not sure how to pass them to next.jdbc so it passes them down to the mariadb jdbc driver

seancorfield21:03:01

If you're using a db-spec hash map, just add them to that. If you're using a JDBC URL, add them to that.

seancorfield21:03:24

If you're using a connection pooling library, you might need to do something different.

seancorfield21:03:06

Bottom line: you'll always get weird TZ behavior if you don't have your JVM and your DB set to the same timezone. It's why we have all our QA and production servers set to UTC at the O/S level so the DB and the JVM inherit that.

lepistane21:03:54

@U04V70XH6 I was looking into https://mariadb.com/kb/en/about-mariadb-connector-j/#timezone-consideration no connection pool lib i tried adding it to url and also map it failed. Example

"localhost/?timezone=UTC"
it failed What do you do with local development? Use java option for UTC timezone?

seancorfield21:03:36

"i tried adding it to url and also map it failed" -- what exactly did you try and how exactly did it "fail"?

seancorfield21:03:48

Also, where is your DB running for development?

jumar05:03:46

If you are interested in learning more, this is a great article: https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/

👍 2
2
lepistane09:03:59

@U04V70XH6 i tried adding time zone like i mentioned from described in the link https://mariadb.com/kb/en/about-mariadb-connector-j/#timezone-consideration like so

{:classname "org.mariadb.jdbc.MySQLDataSource",
 :dbname "dev",
 :dbtype "mariadb",
 :host "localhost/?timezone=UTC",
 :port 3307,
 :password "admin",
 :user "admin"}
and then when i try to execute
{:select [:*]
                                          :from [[:fixture :fms]]}
i get
Execution error (SQLException) at org.mariadb.jdbc.plugin.authentication.standard.CachingSha2PasswordPlugin/process (CachingSha2PasswordPlugin.java:141).
RSA public key is not available client side (option serverRsaPublicKeyFile not set)
So most likely i added this wrong I added it like
:timezone "UTC"
and same error happened. Everything i do i local. So i have DB running locally (UTC +2) JVM process is also local Values in DB are in UTC. Once queried they lose 1 hour.

seancorfield19:03:23

"and same error happened." -- well, the error you showed about password plugin / RSA public key sounds like you're still passing the wrong stuff to the driver, so perhaps share the actual db-spec hash map you're trying now and we'll see what's wrong with it.

lepistane21:03:42

@U04V70XH6 it appears you were right.

{:classname "org.mariadb.jdbc.MySQLDataSource",
 :dbname "dev",
 :dbtype "mariadb",
 :host "localhost",
 :timezone "GMT+1",
 :port 3307,
 :password "admin",
 :user "admin"}
does produce proper results. Problem is i dont want any conversion which will have to tinker with tomorrow. Thank you for your time and patience

2
seancorfield00:03:58

The docs for that :timezone setting seem to say you only need it if your client and server are in different timezones -- which shouldn't be the case when you're running both locally on your machine. Are you running both actually on your machine or is either of them in a VM? If so, you need to ensure that your VM(s) are all in the same TZ.

lepistane06:03:50

DB is actually docker container which is running on my machine Clojure is on the machine itself. Do you think that could cause the difference?

seancorfield15:03:53

Yes, that was exactly what I said above: ensure your Docker container is running in the same TZ as your host machine.

👍 2
lepistane15:03:57

solved with

:jvm-opts ["-Duser.timezone=UTC"]

2