H2 In Memory DB: Set Timezone with JDBC? Java Unit Tests

Question

I'm running into a weird dilemma with h2. We have an in memory h2 db setup, mode a la Oracle, to help with our unit tests. We've set the Timezone of the Application as

@PostConstruct
void started() {
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}

We have our h2 setup as a datasource in our maven/springboot app as below:

datasource.config.url=jdbc:h2:mem:AZ;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=ORACLE;
datasource.config.username=sa
datasource.config.password=
datasource.config.driver-class-name=org.h2.Driver

And I have a test running that's based around timestamp. So I wrote this little mapper to get the DB time from h2:

@Select("Select CURRENT_TIMESTAMP")
String selectCurrentTimestamp();

The issue is that h2 keeps taking the actual system time of what it's running on instead of listening to the timezone settings laid out above. So inserts in our unit tests insert at the correct time, regardless. But Selects based around time seem to get translated based on the system time and thus don't work properly. I've also made the transition to using newer java 8 java.time classes as params to my select functions etc. Still no dice.

I've read a good few posts around this involving hibernate (not being used explicitly) and a few other slightly similar questions. However I haven't found a solution to this particular problem and could use some help.

Who's really the source of this issue, JDBC? Or H2? And depending on that, how could I start down a track to fix it? Is there a string I can provide to the JDBC connection URL? Or another setting to be made?

Answer 1

I ran into this same issue. The hint that set me down the right path came from this answer on a previous post:

Note that you can't change the timezone after the driver has been loaded.

Using PostConstruct in the Application class is too late. The timezone default will get set after the driver has already been loaded, and H2 will already be locked in.

A couple of solutions that can work here:

1. Provide a JVM arg to set the timezone: -Duser.timezone=UTC

2. Set the timezone default before loading the SpringApplication (or before anything else has been loaded):

   public static void main(String[] args) {
      TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
      SpringApplication.run(Application.class, args);
   }
   

   Note: You could also run this in response to the ApplicationContextInitializedEvent, as no other beans have been loaded yet. I'm not sure if there are any downsides to just doing it in Main.