Use LocalDate and LocalDateTime with sqlite, juxt/tick and next.jdbc

db.clj

(ns db
  (:require [tick.alpha.api :as t]
            [next.jdbc.result-set :as rs])

(extend-protocol rs/ReadableColumn
  String
  (read-column-by-index [val rsmeta idx]
    (case (.getColumnTypeName ^ResultSetMetaData rsmeta idx)
      "DATE" (t/date val)
      "DATETIME" (t/date-time (s/replace val " " "T"))
      val)))

db_test.clj

(ns db-test
  "Confirm round trip of (especially) date/datetimes."
  (:require [clojure.test :refer :all]
            [clojure.string :as s]
            [next.jdbc :as jdbc]
            [tick.alpha.api :as t]
            [next.jdbc.result-set :as rs])
  (:import (java.sql Connection)
           (java.time LocalDate LocalDateTime)))

(def con (atom nil))

(defn setup-db [f]
  (reset! con (jdbc/get-connection {:dbtype "sqlite" :dbname ":memory:"}))
  (jdbc/execute! @con ["create table _types (
                          id integer primary key,
                          _default datetime default (datetime('now','localtime')),
                          _date date,
                          _datetime datetime,
                          _truncated datetime,
                          _real real,
                          _int int);"])
  (f)
  (.close ^Connection @con))

(use-fixtures :once setup-db)

(deftest types
  (let [examples [(t/date) (t/date-time) (t/truncate (t/date-time) :hours)
                  (rand) (rand-int 100)]

        response
        (jdbc/execute-one! @con
          (cons "insert into _types
                   (_date, _datetime, _truncated, _real, _int) values (?, ?, ?, ?, ?);"
            examples))

        {:_types/keys [id _default _date _datetime _truncated _real _int]}
        (jdbc/execute-one! @con ["select * from _types;"])

        {:keys [_d _dt _tr]}
        (jdbc/execute-one! @con
          ["select trim(_date) as _d,
                   trim(_datetime) as _dt,
                   trim(_truncated) as _tr from _types;"]
          {:build-fn rs/as-unqualified-lower-maps})]
    (is (= response #:next.jdbc{:update-count 1})
        "One row was written.")
    (is (= examples [_date _datetime _truncated _real _int])
        "Values survive round trip.")
    (is (= _default (t/truncate _datetime :seconds)) ; SQLite loves microseconds...
        "Default local datetime works.")
    (are [v t] (= (type v) t) ; Types are correct.
      id Integer
      _default LocalDateTime
      _date LocalDate
      _datetime LocalDateTime
      _truncated LocalDateTime
      _real Double
      _int Integer)
    (is (= _d (t/format (t/formatter "YYYY-MM-dd") (examples 0)))
        "Date is stored in human-readable format.")
    ; Lop off microseconds on read and change T to space.
    (is (= (-> _dt (subs 0 16) (s/replace \T \space))
           (t/format (t/formatter "YYYY-MM-dd HH:mm") (examples 1)))
        "Datetime is stored in human-readable format.")
    (is (= (-> _tr (s/replace \T \space))
           (t/format (t/formatter "YYYY-MM-dd HH:mm") (examples 2)))
        "Microseconds are not stored if truncated before write.")))

deps.edn

{:deps {org.clojure/clojure {:mvn/version "1.10.1"}

        tick {:git/url "https://github.com/juxt/tick.git"
              :sha "1e14333e3ce142dd3eebb0cca9449a980a924f1e"} ; 20200207

        seancorfield/next.jdbc {:mvn/version "1.0.424"}
        org.xerial/sqlite-jdbc {:mvn/version "3.30.1"}}}