search

seancorfield / next-jdbc

A modern low-level Clojure wrapper for JDBC-based access to databases.
https://cljdoc.org/d/com.github.seancorfield/next.jdbc/
Eclipse Public License 1.0
755 stars 90 forks source link

Add UTC time recommendation to docs #199

Closed seancorfield closed 2 years ago

seancorfield commented 2 years ago

Re: PostgreSQL timestamp/timezone issue and more general timezone issues.

Maybe add notes about setting the database/server/jvm to UTC and always operating in UTC and just translating to user TZ in the UI.

denismccarthykerry commented 2 years ago

Hi Sean, it was me who asked that question on Slack - thanks again for the feedback. Something like this would have helped me - feel free to use as much or as little of this as you want as I appreciate that each individual case will be different.

Working with dates and timezones in databases can be confusing, as you are working at the intersection between the database, the jdbc library and the date library that you happen to be using. A good rule of thumb is to keep timezone-related logic as simple as possible. For example, with Postgres we recommend always storing dates in a Postgres TIMESTAMP (without time zone) column, storing all such timestamps in UTC, and applying your time zone logic separately using application logic. The TIMESTAMP WITH TIME ZONE column type in Postgres stores its date in UTC anyhow, and applications that need to deal with time zones typically require richer functionality than simply adjusting the time zone to wherever the database happens to be hosted. Treat time zone related logic as an application concern, and keep stored dates in UTC.

For example, for a developer using clojure.java-time, saving (java-time/instant) in a timestamp column (and doing any timezone adjustment elsewhere) is a good way to minimize long term confusion.

dcj commented 2 years ago

A good rule of thumb is to keep timezone-related logic as simple as possible. For example, with Postgres we recommend always storing dates in a Postgres TIMESTAMP (without time zone) column, storing all such timestamps in UTC, and applying your time zone logic separately using application logic

I disagree with this recommendation, I have found it safer/better to store timestamps in Postgres as TIMESTAMPTZ (with time zone), so you always know what you have. And yes, I endeavour to write my timestamps in/as UTC regardless.

This is just a random comment, doesn't affect the resolution of this issue, but my 2-cents, for the record....