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.

But it maps the a jvm type that does have a timezone

I believe the postgres jdbc driver ends up using the default timezone of the jvm

(this is why the only sane setup is to have your servers, your databases, and your JVMs all set to UTC explicitly!)

Or store timezones

Is there a way to set all JVMs on my computer to UTC?

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

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