Hi, I’m using the next.jdbc library for my Postgres interactions. Very nice and easy to use I must say -thanks to Sean Corfield for such a great contribution to the community. I am however running into a problem with the famous Postgres Date/timestamp funkery that is flagged in the next.jdbc docs. I’m trying to insert a java-time/zoned-date-time into a postgres TIMESTAMP WITH TIME ZONE field, and I’m getting Execution error (PSQLException) at org.postgresql.jdbc.PgPreparedStatement/setObject (PgPreparedStatement.java:1005). Can't infer the SQL type to use for an instance of java.time.ZonedDateTime. Use setObject() with an explicit Types value to specify the type to use. I read the tips and tricks section of the docs, where it says that you must (:require [next.jdbc.date-time]) to overcome this Postgres limitation, but that hasn’t resolved the problem - I’m getting the same exception after reloading the file with that require included. Is there something else that I must do to get this working? Thanks in advance

Ok I’ve figured this out - I should not be attempting to save a zoned-date-time directly, I should be saving an instant - and Postgres doesn’t actually save TZ data, it just dynamically adjusts TIMESTAMP WITH TIMEZONE value reads to whatever timezone the DB itself is set to. I will stick with plain postgres TIMESTAMP columns, ensure everything is saved in UTC, and do any necessary TZ adjustments elsewhere.

@denis.mccarthy.kerry Glad you figured it out. Date/time stuff is crazy around databases, especially with timezones involved. If you have suggested wording I can add to the PostgreSQL Tips & Tricks section that would have helped you figure this out more quickly, feel free to create an issue on GH.

@denis.mccarthy.kerry I’ve found storing everything as a UTC instant (event at server time) and storing the user’s timezone separately and as you mentioned adjusting the time for user facing display purposes outside the database has been a reasonable approach.

Our domain is consumer facing mobile apps with a monolithic clojure backend server. Might have limitations outside that context.

Yeah, we store everything as UTC as well: we have our servers set to UTC and our databases configured to UTC and we do all our date/times in UTC in Clojure -- and adjust to "client-side" based on a user's timezone where appropriate. It seems to be the only sane solution 😕

Might be worth putting that in as a little opinion piece on time and databases. I’ve honestly found the ones already scattered throughout the documentation really insightful. Three in particular:the link to the article on snake case, the fact that in practice a lot of the time you might want to be using plan instead of execute, and the clarification that the JVM does clean up connections automatically on being shut down (so you don’t need to explicitly close them if you intend to keep them around for the lifetime of the app). Again can’t praise these docs enough.