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 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'"); 

Answer 2

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?