24

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>
Tomasz Nurkiewicz
  • 334,321
  • 69
  • 703
  • 674
Thomas Vervik
  • 4,325
  • 9
  • 36
  • 64
  • 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. – Thomas Mueller Nov 01 '12 at 15:55
  • 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. – Alex R Nov 07 '17 at 02:41

7 Answers7

15

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.

Cristian Vrabie
  • 3,972
  • 5
  • 30
  • 49
11

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.

AbstractVoid
  • 3,583
  • 2
  • 39
  • 39
  • 1
    This works as expected. Adding this property to `application.properties` will set the in-memory DB's timezone to the desired one. – Romeo Sierra Jul 13 '20 at 01:25
  • 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). – G. Demecki Nov 18 '22 at 05:41
8

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.

Dimitar II
  • 2,299
  • 32
  • 33
  • 1
    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. – AnonymousAngelo Apr 23 '19 at 15:37
  • Does only work for application but still breaks tests when running on machine with different timezone. – spyro Nov 11 '21 at 17:54
5

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
MichaelCkr
  • 540
  • 1
  • 7
  • 14
3

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);
Dmitriy
  • 5,525
  • 12
  • 25
  • 38
user666
  • 1,750
  • 3
  • 18
  • 34
  • 1
    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()`. – Mopper Sep 02 '20 at 13:12
1

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)
leonardo12
  • 398
  • 1
  • 7
  • 18
0

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?

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • 5
    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). – Cristian Vrabie Sep 18 '13 at 11:02
  • 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? – Thomas Mueller Sep 18 '13 at 16:59
  • 3
    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](http://www.h2database.com/html/tutorial.html#date_time). Sometimes this is desirable, sometimes it's not. – Cristian Vrabie Sep 18 '13 at 21:29
  • 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 :\\\\ – marcolopes Jul 23 '17 at 02:42
  • And MORE... any TIME field accessed out of the original timezone is wrong! @ThomasMueller, I need help to get around this problem... :| – marcolopes Jul 23 '17 at 03:16