clojure.java.jdbc: How to read postgres dates as localdate when in a non-utc timezone?

Question

When I read postgres date types, I want them to be coerced into joda time LocalDates (or the new java.time localdates). This is because postgres dates does not have time zone info, so I don't want my java or clojure object to add it somehow.

This is the IResultSetReadColumn protocol extension I've set up for this:

(extend-protocol clojure.java.jdbc/IResultSetReadColumn
  java.sql.Date
  (result-set-read-column [v _rsmeta _idx]
    (tc/to-local-date v)))

However, when I try to use it, I get the wrong result:

(DateTimeZone/getDefault)
=> #object[org.joda.time.tz.CachedDateTimeZone 0x60470ff "Europe/Stockholm"]

(jdbc/query db/db ["SHOW TIMEZONE"])
=> ({:timezone "Europe/Stockholm"})

(jdbc/query db/db ["SELECT '2020-01-01'::date"])
=> ({:date #object[org.joda.time.LocalDate 0x75081795 "2019-12-31"]}) ; ARGH!! It changed date!

I suspect this is caused by the dates being converted into java.sql.date, which has timezone info in it somehow. Is there perhaps a way to read the postgres dates directly to avoid this? I've found documentation on the postgres JDBC driver that seems promising but I can't figure out how to implement it in clojure.java.jdbc.

Basically: Is there any way to get my postgres dates out of the database without messing them up, if my default timezone is not UTC?

Answer 1

I figured out how to do it now:

(defn sql-date->LocalDate [v]
      ; Convert a java.sql.Date into a LocalDate.
      ; LocalDates does NOT have any timezone info - bit un-intuitive perhaps.
      ; Neither does the postgres date type.
      ;
      ; Unfortunately, java.sql.Date has a time component, 
      ; which would causes lots of issues if we were to pass it along as is.
      ; How it works is:
      ; 1. The postgres JDBC driver reads the postgres date as a java.sql.Date,
      ;    by setting the jav.sql.Date timestamp to 00.00
      ;    the date of the postgres date in the 
      ;    JVM default timezone (DateTimeZone/getDefault).
      ;
      ; 2. .toLocalDate converts the java.sql.Date to a java.time.LocalDate 
      ;     in the JVM default timezone (DateTimeZone/getDefault).
      ;
      ; 3. Then we convert the java.time.LocalDate to a joda-time LocalDate, 
      ;    as that is what clj-time uses.
      ;
      ; So because we convert both date -> timestamp -> date in the same
      ; timezone, it all evens out.
      ;
      (let [java-time-localdate (.toLocalDate v)]
        (time/local-date (.getYear java-time-localdate)
                         (.getValue (.getMonth java-time-localdate))
                         (.getDayOfMonth java-time-localdate))))

The most important thing is NOT to use clj-time.coerce's to-localtime function. I'm not sure what it does but anyways it messes the day up when there are timezones involved.