Fork me on GitHub

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 ( 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 []) 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.


Thanks for the reply Sean. As others have said the documentation as it is is very good compared to the vast majority of OS libraries, so kudos for that. As an addition, an example of how to use Java-time and next.jdbc to insert a time stamp into Postgres would have been ideal for me. I will put together a few lines on that topic and you can decide whether it is of sufficient general interest to go in. Thanks again for the great library and your support in this forum.


@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.

😊 1