PostgreSQL 9.2 JDBC driver uses client time zone?

Question

I've run into an interesting challenge using a PostgreSQL database with the PostgreSQL JDBC driver. It seems that the latest version of the driver, 9.2, uses the client time zone when performing date/time matches.

This becomes a problem when the server (JasperReports Server) is set to UTC and the database server is set to US/Eastern.

If I run the following query from a client set to the UTC time zone I get different results using the 9.0 JDBC driver and the 9.2 JDBC driver.

select now(), extract(timezone FROM now()), current_setting('TIMEZONE'), now()-interval '1 hour' as "1HourAgo"

Results using 9.0 JDBC driver:

now                         date_part   current_setting     1HourAgo
2013-08-26 15:33:57.590089  -14,400     US/Eastern          2013-08-26 14:33:57.590089

Results using 9.2 JDBC driver:

now                         date_part   current_setting     1HourAgo
2013-08-26 15:41:49.067903  0           UTC                 2013-08-26 14:41:49.067903

This is causing a WHERE statement in a query to return incorrect results. For example,

WHERE end_time between now() - interval '1 hour' and now()

works as expected using the 9,0 driver but returns no results using the 9,2 driver as the driver appears to be offsetting the value of end_time to match UTC (the client's time zone). The following is a workaround, but an ugly one:

WHERE end_time at time zone 'EDT' between now() - interval '1 hour' and now()

Questions:

1. Has anyone else run across this before?
2. Is there an explanation for this change in behavior? I haven't been able to find anything in the JDBC release notes
3. Any advice on how to work around this other than rolling back the driver to an older version?

Thanks!

Answer 1

I don’t know JasperReports, but generally speaking, using the JSR-310 (Java 8) date/time types (supported by JDBC 4.2) should work without having to worry about time zone mismatches between database server and client.

See Using Java 8 Date and Time classes in the PostgreSQL JDBC driver documentation.

Answer 2

Just in case others struggle with this - you can edit the "conf" file that launches SQLDeveloper and add the following line :

AddVMOption -Duser.timezone=UTC

I found this file on my machine at :

/opt/sqldeveloper/ide/bin/ide.conf

Thanks to @a_horse_with_no_name for pointing me in the right direction with a comment in one of the other answers

Answer 3

The use of the local timezone as the default timezone is required by the JDBC standard (and API documentation), and is made explicit by PreparedStatement.setTimestamp. However it also applies to all other areas where JDBC sets or retrieves time related data.

See also my answer to Is java.sql.Timestamp timezone specific?

Answer 4

I just ran into this issue myself. I verified that the postgres jdbc driver is indeed picking up the connection timezone from the jvm, and I wasn't able to find a way to override this behavior. It really would be nice if they provided a jdbc url connection parameter for this purpose.

As a workaround, I discovered that my connection pool library (HikariCP) can execute a sql statement for each new connection:

hikariConfig.setConnectionInitSql("set time zone 'UTC'");