[h2] Re: Timestamp's value is different in different time zones
This is *.h2.db file( So, the issue is solved: PageStore backend cannot read persisted timestamps back properly if there is any difference between DST transition rules. Thank you very much for this and quick responses On Tuesday, March 14, 2023 at 5:18:30 PM UTC+2 Evgenij Ryazanov wrote: > Take a look on file name extension, if it is .h2.db, this is a file from > PageStore, if it is .mv.db a modern MVStore backend is used. > > H2 1.4.192 is too old and doesn't support JSR-310 data types yet, their > initial support was added only in H2 1.4.193. > > If your file is in MVStore format, you can read the original datetime > value with ResultSet.getString(column) in 1.4.192. > -- 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/d2939485-0f06-4e90-b7bf-2f657a723e0fn%40googlegroups.com.
[h2] Re: Timestamp's value is different in different time zones
Take a look on file name extension, if it is .h2.db, this is a file from PageStore, if it is .mv.db a modern MVStore backend is used. H2 1.4.192 is too old and doesn't support JSR-310 data types yet, their initial support was added only in H2 1.4.193. If your file is in MVStore format, you can read the original datetime value with ResultSet.getString(column) in 1.4.192. -- 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/ae329b60-ffa7-4115-a9b9-825f68e513d2n%40googlegroups.com.
[h2] Re: Timestamp's value is different in different time zones
>From Documentation: Storage Engine for H2 For H2 version 1.4 and newer, the MVStore is the default storage engine (supporting SQL, JDBC, transactions, MVCC, and so on). For older versions, append ;MV_STORE=TRUE to the database URL. hm... should be MVStore already. Then, probably, something else On Tuesday, March 14, 2023 at 11:48:26 PM UTC+9 Nazar Viliura wrote: > This sounds very likely as a reason! I'm using H2 v.1.4.192. How do I > check if it uses PageStore backend? Also, is there any documentation which > specifies this problem, if you know? Thanks in advance > > On Tuesday, March 14, 2023 at 11:43:18 PM UTC+9 Evgenij Ryazanov wrote: > >> > Unfortunately, I should be using an old version of jdbc driver >> >> If you use some old unsupported version of H2 with PageStore backend, you >> need to set the same JVM time zone as it was on system where database file >> was created. This backend had very problematic storage format for datetime >> values. It cannot read persisted timestamps back properly if there is any >> difference between DST transition rules. MVStore backend isn't affected by >> that problem. >> > -- 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/85ec86c3-bb16-442b-9b81-ed9de7afcea1n%40googlegroups.com.
[h2] Re: Timestamp's value is different in different time zones
This sounds very likely as a reason! I'm using H2 v.1.4.192. How do I check if it uses PageStore backend? Also, is there any documentation which specifies this problem, if you know? Thanks in advance On Tuesday, March 14, 2023 at 11:43:18 PM UTC+9 Evgenij Ryazanov wrote: > > Unfortunately, I should be using an old version of jdbc driver > > If you use some old unsupported version of H2 with PageStore backend, you > need to set the same JVM time zone as it was on system where database file > was created. This backend had very problematic storage format for datetime > values. It cannot read persisted timestamps back properly if there is any > difference between DST transition rules. MVStore backend isn't affected by > that problem. > -- 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/c4181e58-264f-4353-b40d-d8b9830759f8n%40googlegroups.com.
[h2] Re: Timestamp's value is different in different time zones
> Unfortunately, I should be using an old version of jdbc driver If you use some old unsupported version of H2 with PageStore backend, you need to set the same JVM time zone as it was on system where database file was created. This backend had very problematic storage format for datetime values. It cannot read persisted timestamps back properly if there is any difference between DST transition rules. MVStore backend isn't affected by that problem. -- 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/2f772404-740a-410c-992b-c048b8ec2430n%40googlegroups.com.
[h2] Re: Timestamp's value is different in different time zones
thanks for a quick response! My task is a data migration and I'm working with TIMESTAMP (without time zone) field as this H2 DB was created in another system and I cannot affect this anyhow. So, as you've said, the Timestamp field should represent local date, without time zone info, so it is expected to have the same value independently on system's time zone. But if you look at my initial examples, the same value is represented differently. That is the main problem! (yes, in Asia/Tokyo the value is not adjusted. But when I read it in Europe/Paris, I still can see the adjustment. So, it looks like, if the value was saved and read in the same time zone, it is not got adjusted later) Unfortunately, I should be using an old version of jdbc driver, as when I try to read the value as LocalDateTime , it returns just as NULL resultSet.getObject(3, LocalDateTime.class)) Reading the value the following way makes no sense for obvious reason: resultSet.getTimestamp(4).toLocalDateTime() Maybe, all those conversion issues are just a result of mentioned by you defective Timestamp class in general? Could you confirm? Regards, Nazar On Tuesday, March 14, 2023 at 7:26:38 PM UTC+9 Evgenij Ryazanov wrote: > 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/cb80520f-7028-4703-8a45-d10302744667n%40googlegroups.com.
[h2] Re: Unique index or primary key violation
Hi! It doesn't look like an original error from H2, H2 produces messages like Unique index or primary key violation: "PRIMARY_KEY_6 ON PUBLIC.AD(ACCU_ID, TARGET_ID, REPLAY_ID) VALUES ( /* key:1 */ 2, 3, 4), where 1 is an internal row number (_ROWID_) and other values are values of primary key columns. Please note that H2 1.4.192 is a very old unsupported version of H2 database. -- 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/af3fee93-0f96-4c7b-8886-c49a8bcac386n%40googlegroups.com.
[h2] Re: Timestamp's value is different in different time zones
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.
[h2] Unique index or primary key violation
Hi, I am facing the following error when writing to a table via hibernate: 03/11/23 19:44:52 808 ERROR SqlExceptionHelper: Unique index or primary key violation: "PRIMARY_KEY_6 ON PUBLIC.AD(ACCU_ID, TARGET_ID, REPLAY_ID) VALUES (8, 24, 1, 7)"; SQL statement: insert into ad (end_time, start_time, accu_id, target_id, replay_id) values (?, ?, ?, ?, ?) [23505-192] I wonder what the 7 in (8, 24, 1, 7) means. (ACCU_ID, TARGET_ID, REPLAY_ID) has 3 columns (the primary key for ad table). It is a bit strange why a fourth number shows up. Thanks -- 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/6a3606a7-a4bb-4ddf-bfa9-5d5c54e173e6n%40googlegroups.com.
[h2] Timestamp's value is different in different time zones
Hi, I have a task to migrate H2 db into Postgres DB and found some not constant behavior of timestamp fields when data is read. There are 2 columns, one with summer time (DST on) and on with winter time (DST off). This is how those are rendered when system's time zone is set to UTC: 2022-07-14 08:23:51.836 and 2022-11-24 04:04:25.822 And this is how it looks like in different time zones (DST on and off): Timezone DST ON DST OFF Europe/Amsterdam, UTC+1 2022-07-14 09:23:51.836 (where DB was created) 2022-11-24 04:04:25.822 - Europe/Kyiv, UTC+2 2022-07-14 08:23:51.836 2022-11-24 03 :04:25.822 - Asia/Volgograd, UTC+3 2022-07-14 07:23:51.836 2022-11-24 03:04:25.822 Asia/Bagdad, UTC+32022-07-14 08:23:51.836 2022-11-24 04:04:25.822 Asia/Singapore, UTC+8 2022-07-14 08:53:51.836 2022-11-24 04:34:25.822 When debugging of org.h2.table I can see that timestamp consists of 2 parts: date and time. But when I do the conversion with H2 code, I can see that those values are already adjusted depending on which time zone the system is. The question is why it is not consistent with UTC offset of the time zone? Is there any algo on how to calculate this adjustment? Thanks! -- 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/d8da729d-a51b-4207-97fe-418ed7ec5b93n%40googlegroups.com.
[h2] Contribution statement
This statement is in relation to https://github.com/h2database/h2database/pull/3753 and any other contributions to H2 project from mwisni...@gmail.com unless otherwise stated: I wrote the code, it's mine, and I'm contributing it to H2 for distribution multiple-licensed under the MPL 2.0, and the EPL 1.0 (https://h2database.com/html/license.html). -- 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/e8dfb5f6-96b8-4a98-ab29-c7c266a07c4an%40googlegroups.com.
