This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2019-06-05
Channels
- # announcements (10)
- # beginners (59)
- # calva (172)
- # cider (13)
- # clj-kondo (1)
- # cljdoc (10)
- # cljs-dev (4)
- # cljsrn (65)
- # clojure (144)
- # clojure-europe (2)
- # clojure-italy (26)
- # clojure-losangeles (1)
- # clojure-nl (14)
- # clojure-spec (26)
- # clojure-uk (91)
- # clojurescript (75)
- # core-async (53)
- # cursive (11)
- # datomic (16)
- # fulcro (42)
- # graalvm (29)
- # graphql (9)
- # kaocha (3)
- # leiningen (22)
- # off-topic (26)
- # qa (1)
- # re-frame (3)
- # reagent (7)
- # reitit (10)
- # rewrite-clj (56)
- # robots (1)
- # shadow-cljs (107)
- # spacemacs (10)
- # specter (5)
- # sql (15)
- # tools-deps (39)
- # vim (11)
Does JDBC apply any sort of timezone conversions to a java.sql.Timestamp
before querying?
@kenny I suspect that depends on the exact DB column type and the JDBC driver you are using. clojure.java.jdbc
and next.jdbc
do not perform any sort of conversion -- they just pass values through to the driver.
Any idea if Postgres + timestamp
does that? From the docs, timestamp
isn't supposed to have a timezone.
(this is why the only sane setup is to have your servers, your databases, and your JVMs all set to UTC explicitly!)
AFAIK there’s no single external configuration option that would be guaranteed to work across all JVMs. With Oracle JVM:s there’s undocumented _JAVA_OPTIONS
environment variable where you can define system properties like "-Duser.timezone=UTC"
. However safest way is probably to always run java like java -Duser.timezone=UTC ...
In postgres there’s TIMESTAMP
and TIMESTAMPTZ
and you should always use the latter. Here’s a nice resource about postgres and timezones http://blog.untrod.com/2016/08/actually-understanding-timezones-in-postgresql.html
Note, that TIMESTAMPTZ
still doesn't store the timezone - all it does is it tries to convert values when writing/reading from/to DB.
imho it's better to be explicit about timezones in the code than to rely on some global settings
e.g. this is something I've found in the project:
; [jdbc.proto :as jdbc-proto]
(extend-protocol jdbc-proto/ISQLResultSetReadColumn
Timestamp
(from-sql-type
[this _conn _metadata _index]
; Roundtrip using .toLocalDateTime is needed because of timezone issues:
; if we directly call .toInstant on the java.sql.Timestamp
; it will construct an Instant based on timezone of the JVM instead of UTC.
(-> this
.toLocalDateTime
(.toInstant ZoneOffset/UTC)))
)
> In postgres there’s TIMESTAMP
and TIMESTAMPTZ
and you should always use the latter
I've seen the opposite opinion. E.g. see http://www.date4j.net/ docs which recommend against timestampZ:
PostgreSQL has 2 data types named TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE. These columns store time zone/offset information, right? Wrong. Neither a time zone nor an offset is stored in these fields. From their documentation:
"All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client."
So, what you have here is a misrepresentation of what is being stored. The name of the data type clearly implies that a time zone/offset is being stored, but that's clearly not the case. There is unequivocally no explicit time zone/offset is stored in these columns. None whatsoever. Rather, an implicit offset is used, and a calculation is applied to the data, using particular policies defined by the database, involving the difference between 2 offsets.
...
* in your database, use columns having data types which do not attempt to manage time zones for you.
My thinking is that it’s better to be explicit about the timezone whenever possible and secondly only to use UTC timestamps everywhere except UI. It’s true that TIMESTAMPTZ
is a misleading name but at least it (hopefully) encourages the developer to think “what timezone is this timestamp and what should it be..”