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
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