Read and Write H2 DB's TimestampWithTimeZone from Clojure by converting them to instances of java.time.Instant. Requires Java 8.

db.clj

(ns yourns.db
  (:require [clojure.java.jdbc :as jdbc]
            ;; note the java-time library from
            ;; https://github.com/dm3/clojure.java-time
            java-time)
  (:import org.h2.util.DateTimeUtils))


(defn- h2-timestamp-with-timezone->instant
  "Convert a h2 `TimestampWithTimeZone` to a `java.time.Instant`."
  [^org.h2.api.TimestampWithTimeZone h2-timestamp]
  (.toInstant (DateTimeUtils/convertTimestampTimeZoneToTimestamp
               (.getYMD h2-timestamp)
               (.getNanosSinceMidnight h2-timestamp)
               (.getTimeZoneOffsetMins h2-timestamp))))


;; extend the jdbc protocol so that values are automatically converted
;; to `Instant`s upon retrieval from the DB

(extend-protocol jdbc/IResultSetReadColumn
  org.h2.api.TimestampWithTimeZone
  (result-set-read-column [v _2 _3]
    (h2-timestamp-with-timezone->instant v)))

;; while clj-time has to implement this also for writing to the
;; db (https://github.com/clj-time/clj-time/blob/master/src/clj_time/jdbc.clj),
;; we don't have to to this for h2 - just insert `Instant`s and it
;; works!


;; let's walk through some quick examples!

(comment
  (def ^:private db-spec
    "The database spec, needed for connecting to the H2 DB. Let's use an
  in-memory DB for our purposes here."
    {:classname   "org.h2.Driver"
     :subprotocol "h2:mem"
     :subname     "test_db;DB_CLOSE_DELAY=-1"
     :user        "sa"
     :password    ""})
  ;; => #'backend.db/db-spec

  ;; create a table with a TIMESTAMP WITH TIME ZONE field
  (jdbc/execute! db-spec "CREATE TABLE food (id IDENTITY PRIMARY KEY, timestamp TIMESTAMP WITH TIME ZONE)")
  ;; => [0]

  ;; insert something
  (jdbc/insert! db-spec "food" {:timestamp (java-time/instant)})
  ;; => ({:id 1})

  ;; it works!

  ;; now, let's retrieve it and see whether we're receiving our
  ;; timestamp as `Instant`s:

  (jdbc/query db-spec "SELECT * FROM food")
  ;; => ({:id 1,
  ;;      :timestamp
  ;;      #object[java.time.Instant 0x51506bf4 "2018-10-03T21:05:12.513Z"]})

  ;; success!
)