Hi! There are two different timestamp data types in the SQL Standard: TIMESTAMP (TIMESTAMP WITHOUT TIME ZONE) and TIMESTAMP WITH TIME ZONE.
TIMESTAMP [ WITHOUT TIME ZONE ] has YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND fields. TIMESTAMP WITH TIME ZONE additionally has TIMEZONE_HOUR and TIMEZONE_MINUTE fields. The SQL Standard determines their relation to local time or UTC time in the following way: A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE, may represent a local time, whereas a datetime value of data type TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE represents UTC. H2 strictly follows the SQL Standard here. The TIMESTAMP data type in H2 represents local date and time, the TIMESTAMP WITH TIME ZONE represents a timestamp with some exactly known UTC offset and this offset is preserved. (Actually H2 additionally supports time zones with seconds in their offsets, but it doesn't matter here.) SET TIME ZONE 'Europe/Paris'; CREATE TABLE TEST(T1 TIMESTAMP, T2 TIMESTAMP WITH TIME ZONE); INSERT INTO TEST VALUES(TIMESTAMP '2023-02-01 01:00:00', TIMESTAMP WITH TIME ZONE '2023-02-01 01:00:00+01:00'); TABLE TEST; > H2: 2023-02-01 01:00:00 | 2023-02-01 01:00:00+01 > PostgreSQL: 2023-02-01 01:00:00 | 2023-02-01 01:00:00+01 SET TIME ZONE 'Asia/Tokyo'; TABLE TEST; > H2: 2023-02-01 01:00:00 | 2023-02-01 01:00:00+01 > PostgreSQL: 2023-02-01 01:00:00 | 2023-02-01 09:00:00+09 You can see that H2 and PostgreSQL work more or less in the same way, but PostgreSQL doesn't preserve time zone offset in its TIMESTAMP WITH TIME ZONE data type and converts UTC to local time zone instead. Anyway, 2023-02-01 01:00:00+01 and 2023-02-01 09:00:00+09 represent the same UTC value. So you need to choose a proper data type depending on your needs. If you need to store local values and they should stay the same when time zone is changed, use the TIMESTAMP data type, but beware of DST transitions. If you need to hold exact absolute values, use TIMESTAMP WITH TIME ZONE. TIMESTAMP values should be read and set as java.time.LocalDateTime. TIMESTAMP WITH TIME ZONE values should be read and set as java.time.OffsetDateTime, but H2 also supports java.time.Instant and java.time.ZonedDateTime. Never use java.sql.Timestamp, this defective by design class represents a local datetime value, but it holds it internally in UTC and it doesn't know the exact time zone to display it properly. It also has other problems with historic dates. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/7521d99d-64d3-48f1-9db8-a79a73214d69n%40googlegroups.com.
