Clojurians Log v2

TZ stuff is a pain in every DB. The only advice I can give is use UTC for everything, including the O/S on all your servers...

yeah I agree UTC for everything is the way to go—just a bit harder to do when doing dev on my personal machine, which is not set to UTC

I start my JVM locally with the UTC timezone. And I run services via Docker locally all set to UTC.

Ah right I totally forgot you can set the JVM timezone! And I should really get around to setting up docker for this... I'm still early in development so haven't really worried about getting it deployed yet

though fwiw I did manage to get around the TZ issue I was having by storing the dates in sqlite as unix epoch millis, but I would definitely prefer a more human readable version

I suppose next.jdbc doesn't have a way to distinguish a date-containing string field from any regular string field, but I still feel like I must be able to somehow easily have the conversion done in a layer below the app logic 🤔

Check out the docstring of next.jdbc.result-set/builder-adapter. By default, next.jdbc relies on the implementation of ResultSet.getObject to provide the right types. Since SQLite doesn't have dates, only strings, getObject there returns strings for dates: https://github.com/xerial/sqlite-jdbc/blob/ec0a52436f740357c6315accaa507c33c2e1ffe8/src/main/java/org/sqlite/jdbc3/JDBC3ResultSet.java#L524-L525 But you can ask next.jdbc to call getDate on the right columns instead. Note that there's a default date format that must be used for getDate to work. But you can also alter that format.

thank you! so I've been looking in the right place, but just didn't quite have it figured out. I got it working with the following:

(def timestamp-columns [:expires-at :created-at])

(defn- timestamp-column-reader [builder rs i]
  (let [col-name (nth (:cols builder) (dec i))
        value (if (some #(= col-name %) timestamp-columns)
                (.getDate rs i)
                (.getObject rs i))]
    (rs/read-column-by-index value (:rsmeta builder) i)))

(def builder-fn (rs/builder-adapter
                 rs/as-unqualified-kebab-maps
                 timestamp-column-reader))

however I'm not sure if this is really any nicer than just doing the conversion after fetching from the db... pro is I don't have to worry about the date types myself, the driver does that. Con is the code is a bit more obtuse imo

given the popularity of sqlite, I'm a bit shocked this isn't built in somewhere at a lower level. But maybe I'm just going about this all wrong or something

Does SQLite provide column type information? There are some tests in next.jdbc around BIT/BOOL handling that auto convert by type (not name) for SQLite that might help you here.

I’m brand new to using SQLite, so frankly not sure but I’ll have to look into it. I do have the relevant columns declared as TIMESTAMP, but I’m not sure if SQLite stores that fact somewhere or just treats it at another way of spelling TEXT

https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.939/doc/getting-started/tips-tricks#sqlite should get you started

aha that works perfectly! the column type name is in fact preserved as TIMESTAMP 😌

ah well now I'm actually running into time zone issues... the xerial/sqlite-jdbc driver seems to be assuming some wrong stuff about time zones. Though it seems it's configurable through it's configuration—are the xerial/sqlite-jdbc configuration options accessible through next.jdbc or do I have to figure out some other way to set them? I haven't been able to find anything about that type of config in the docs so far