23

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!

JTShyman
  • 331
  • 1
  • 2
  • 4

5 Answers5

15

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'");
JimN
  • 3,120
  • 22
  • 35
  • 7
    The JVM uses the time zone reported by the OS - if that's not the right you should probably change the OS settings. But you _can_ override this by specifying `-Duser.timezone=UTC` when starting the JVM –  Jun 16 '15 at 22:18
  • 5
    Sure, but you don't always want to use the same timezone as the jvm. – JimN Jun 17 '15 at 02:13
  • I was able to do this by going into my "/opt/sqldeveloper/ide/bin/ide.conf" and adding "AddVMOption -Duser.timezone=UTC" – Mattiavelli Nov 13 '17 at 23:52
  • it seems to have no effect to me @JimN – deFreitas Nov 08 '19 at 17:53
  • @JimN 's solution can also be implemented using C3P0. You need to implement the `com.mchange.v2.c3p0.ConnectionCustomizer` interface (`onAcquire` method) and set the `c3p0.connectionCustomizerClassName` configuration property. – Emmanuel Guiton Jan 06 '21 at 14:59
5

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?

Community
  • 1
  • 1
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thanks for your answer, Mark. However, it doesn't really answer my question which was specific to changes in the JDBC driver for PostgreSQL between 9.0 and 9.2. 9.2 seems to be picking up the _client_ time zone, and not the _server_ timezone where the driver is located, while 9.0 does not. This is a severe change in the way the driver works, regardless of what the standard should be. – JTShyman Sep 05 '13 at 15:46
  • I skimmed through the release notes of the JDBC driver, but I didn't see anything that indicated a change. I suggest you ask on the pgsql-jdbc mailinglist. – Mark Rotteveel Sep 05 '13 at 17:03
  • Just in case: [this commit](https://github.com/pgjdbc/pgjdbc/commit/9d6b7930e660b8e0f750920c791197c2257f1695#diff-1077801ab85f2e686d05f7b319122ff8R104) adds `TimeZone` session parameter at the connection stage, and it makes DB to use client timezone for calculations where it cannot tell which timezone the data is in. – Vladimir Sitnikov Jul 12 '18 at 12:24
4

Short version try: 

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

Hi we ran across this issue with client and server timezone miss match We needed our unit tests to be run using "UTC". By looking at the source code for postgres jdbc driver (and this being test code) we "fixed" it by changing the TZ inside of the jvm before obtaining a connection.

Konstantin
  • 3,626
  • 2
  • 33
  • 45
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

Mattiavelli
  • 888
  • 2
  • 9
  • 22
0

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.

Martin
  • 2,573
  • 28
  • 22