Clojurians Log v2

solved with

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

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

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

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

Oke so mariadb timestamp is UTC always

My current is GMT+2

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

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

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

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)))

solve which problem?

stored in db: 2023-01-02 135200.000 i query it it becomes 2023-01-02 125200.000

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

how do i do this?

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

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

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

it isn't something next.jdbc is doing

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

it is something the mariadb jdbc driver is doing

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

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.

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

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.

@seancorfield 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"

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

Also, where is your DB running for development?

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/

@seancorfield 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"}

{:select [:*]
                                          :from [[:fixture :fms]]}

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)

:timezone "UTC"

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

@seancorfield 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"}

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.

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?

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