Set default timezone H2 database

Question

How can I explicitly set the time zone H2 should use? Now it gets the timezone to use from the underlying OS. I would assume there existed an extra parameter I would add to the connection string ala the one I have below.

db.url=jdbc:h2:mem:mybipper;MVCC=true;<timezone=UTC>

Archaize

What problem do you want to solve? I'm not aware that H2 uses a timezone (at least not a current version of H2) for most operations. – Perceivable

When storing timestamps in local U.S. timezones, Daylight Savings Time causes loss of information when the timestamp is inside of the "rollback hour", where every possible timestamp value occurs twice in the span of 2 hours. Common workaround is to set the database to UTC, or use strings/longs to encode the date, or add a Timezone Offset field which compensates for DST. – Celle

Answer 1

Apparently you don't have a parameter on the connection but the database will use the timezone of the JVM where the driver is loaded so you can set -Duser.timezone=UTC. Note that you can't change the timezone after the driver has been loaded.

Erikerika

Answer 2

What helped me was to set timezone config for JDBC instead of JVM, which also seems more reasonable and cleaner way, as it affects only the database instead of the whole JVM:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

My answer to the other question might help with additional info.

Oneidaoneil

This works as expected. Adding this property to application.properties will set the in-memory DB's timezone to the desired one. – Myxomycete

Though I like this parameter and it's very useful - but it's not enough alone. The H2 has to be also instructed to use UTC, see @MichaelCkr answer. Moreover when using DBs other than H2, please consider the following update xx set some_time=now();. The now() is not evaluated by JDBC until execution, so it does not use the UTC, but the connection's or db's timezone (it depends). – Jacquenette

Answer 3

You can manipulate the JVM time zone, before interacting with the database:

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

Unfortunately, H2 does not support time zone per connection... yet.

Activity

Thank you. I added this in the @BeforeClass method of my tests which rely on Java.Instant and an H2 DB. The Instant generates UTC timestamps while the H2 DB converts it to UTC+2. – Pardoes

Does only work for application but still breaks tests when running on machine with different timezone. – Pelage

Answer 4

In a H2 database of version 2.x, it is possible to set a timezone in the jdbc url.

This is as well listed in the h2database documentation. The example there says jdbc:h2:./test;TIME ZONE='1:00', which has not worked for me.

A working solution, actually with UTC, is

jdbc:h2:mem:./my_database;TIME ZONE=UTC

Caesar

In the H2 library I saw that they check if provided Zone is in ZoneId.SHORT_IDS. I use connection like: jdbc:h2:mem:mydatabase;TIME ZONE=CET (for Central European Time) and it worked – Alathia

Answer 5

H2 uses JVM timezone and it affects your Date calculation. If you are using it in Junits for example, you can set a certain timezone then re-put initial value when done. Example:

System.setProperty("user.timezone", "GMT-3");
TimeZone.setDefault(null);

Derrik

Just wanted to say this answer using System.setProperty("user.timezone", "GMT"); did the trick for me. H2 didn't pick up anything I did through TimeZone.setDefault(). – Housum

Answer 6

I suppose you see such "misbehaviour" when reading types Date or DateTime.

Apparently H2 uses different time zone information during write and read. Providing a calendar with expected timezone returns the expected values for me:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone(ZoneOffset.UTC))
resultSet.getDate("dateColumn", cal)

Bantu

Answer 7

You can't set the timezone for the database.

But, I'm not aware that H2 uses a timezone (at least not a current version of H2) for most operations.

So, what problem do you want to solve?

Perceivable

If you export some data from MySql the date-times will be in UTC format. If you then import that into a H2 instance that is not on a UTC server (like your local machine when running tests), H2 will convert the date-times thinking they're in your local machine zone. Data will not match anymore. You can edit the SQL to add +00:00 to all date-times which will fix the problem but then this is no longer recognised by MySql (some versions). – Erikerika

Well, this very much sounds like a problem on the MySQL side. How do you currently export the data? Maybe you want to ask another question on how to export using the local timezone? – Perceivable

Probably I explained this badly. Same thing happens if you're exporting from H2 on a timezone then re-importing in a different timezone. It's because the datetime has no timezone in database. This is how H2 was designed and it's documented in their docs. Sometimes this is desirable, sometimes it's not. – Erikerika

I have a very BIG problem because of this behavior... TIME data is not consistent... because of DST? Timezone change? now i have many databases with sensible and important TIME data (used to digital sign information) completely messed up :\\\\ – Languet

And MORE... any TIME field accessed out of the original timezone is wrong! @ThomasMueller, I need help to get around this problem... :| – Languet

Answer 8

In the H2 library I saw that they check if provided Zone is in ZoneId.SHORT_IDS. I used connection like:

jdbc:h2:mem:mydatabase;TIME ZONE=CET

(for Central European Time) and it worked.

Alathia

Answer 9

For maven users, you cass pass the argument to the test profile :

  <profile>
  <id>test</id>
  <build>
    <plugins>
      <plugin>
        <artifactId>maven-failsafe-plugin</artifactId>
        <version>${maven-failsafe-plugin.version}</version>
        <configuration>
          <argLine>-Duser.timezone=UTC</argLine>
        </configuration>
      </plugin>
    </plugins>
  </build>
</profile>

Frontispiece