How to store LocalDateTime in h2 db?-CodePudding

What is the proper way for storing LocalDate instances in h2 db?

I am using data type TIMESTAMP but getting a strange behaviour depending on the user.timezone java process started with.

When I insert a TIMESTAMP with timeZone America/Toronto - and read it back with timeZone UTC - there will be a 1 hour difference for 2021-05-01 but not for 2021-01-01.

Steps to Reproduce

Make sure you have h2-1.4.196.jar in the current folder. Start h2 console with:

java -Duser.timezone=America/Toronto -cp h2*.jar org.h2.tools.Console

For JDBC URL I am using, not all are relevant I assume but MV_STORE=FALSE and MVCC=FALSE should be important.

jdbc:h2:~/mydb;DATABASE_TO_UPPER=FALSE;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=60000;CACHE_SIZE=16384;AUTO_SERVER=TRUE;MV_STORE=FALSE;MVCC=FALSE

Run the following query to create a new table:

CREATE TABLE foo (  
   time TIMESTAMP NULL, 
);

Insert two rows:

INSERT INTO foo VALUES (TIMESTAMP '2021-05-01 00:00:00.0')
INSERT INTO foo VALUES (TIMESTAMP '2021-01-01 00:00:00.0')

Verify you inserted data successfully, which returns:

SELECT * FROM foo;
time  
2021-05-01 00:00:00.0
2021-01-01 00:00:00.0

Now stop the java process you started and this time run it with the following, note the user.timezone is different:

java -Duser.timezone=UTC -cp h2*.jar org.h2.tools.Console

Connect using the same URL above and run the same SELECT query above. Observe the hour difference in the result only for the first entry.

SELECT * FROM foo;
time  
2021-04-30 23:00:00.0
2021-01-01 00:00:00.0

CodePudding user response：

On the 28th of March 2021 at 02:00am time has been forwarded to 03:00am in some countries. This is the somewhat artificial daylight saving performed in many countries of the world (BTW, to be fair it is an achievement of the current Turkish president that Turkey [now Türkiye] is not playing with the tine anymore - his only achievement IMO). As a result the 01.05.2021 00:00 is actually the 30.04.2021 23:00 (BTW both days are national holidays in Vietnam, 30.04.1975 marking the end of that somewhat pointless Vietnam war - Vietnam never played with the time).

Toronto has daylight savings.

H2 is ignoring user.timezone, I suppose. But not entirely. Based on xerx593 comments, I agree, it's a bug.

Abolish daylight savings: https://twitter.com/VitalikButerin?s=20&t=FiPQedQk0YY1ywzPPA6VlA

CodePudding user response：

You're using an entirely outdated and unsupported version of H2 database, even more, you're using a legacy storage backend in it, PageStore (unlike MVStore) doesn't really support changes in JVM timezone.

You need to use the default MVStore backend and you also should consider an upgrade, support of JSR-310 Java data types (java.time.*) in H2 1.4.196 has various known issue.