sql 2021-06-10 | Slack Archive

mccraigmccraig14:06:17

i'm using next.jdbc with hikari and postgres and i'm having what looks like some timezone related problems - is there any way to set the connection timezone for connections gotten via the pool ? HikariConfig.setConnectionInitSql seems ideal, but i'm not even creating a HikariConfig object, just (connection/->pool HikariDataSource db-spec)

mccraigmccraig15:06:43

given up, just forced the JVM to UTC, which matches all our production stuff anyway

seancorfield16:06:29

See https://github.com/seancorfield/next-jdbc/issues/138 — that’s why jdbc-url got added (but there’s a bug affecting SQL Server usage right now — already fixed on develop but not released).

mccraigmccraig16:06:52

ah, ok, that makes sense

seancorfield16:06:05

HikariCP has some weird blind spots. Like: using username instead of user — even though all the JDBC drivers use user 😐

seancorfield16:06:20

(and c3p0 uses user)

mccraigmccraig17:06:42

👍 thanks @U04V70XH6

Cam Saul21:06:12

@U0524B4UW for Postgres you can always just .execute

SET SESSION TIMEZONE TO 'US/Pacific';

You could wrap the DataSource you pass to Hikari and have it do that in getConnection when it creates new connections to add to the pool

Cam Saul21:06:44

e.g. something like

(defn wrapped-data-source ^javax.sql.DataSource [^javax.sql.DataSource original-data-source]
  (reify javax.sql.DataSource
    (getConnection [_]
      (let [conn (.getConnection original-data-source)]
        (try
          (with-open [stmt (.createStatement conn)]
            (.executeUpdate stmt "SET SESSION TIME ZONE 'US/Pacific';"))
          (catch Throwable e
            (.close conn)
            (throw e)))
        conn))))

Cam Saul21:06:41

that works for me locally with Postgres + C3P0 at least.

👍 2

mccraigmccraig20:06:30

good idea, thanks @U42REFCKA

2021-06-10

Channels