pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: alexbishop1 (@alexbishop1) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: Re: [pgjdbc/pgjdbc] issue #1390: LocalDateTime has invalid string serialization
Date: Thu, 24 Sep 2020 23:04:43 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
Here’s a test case:
```java
public static void main(String[] args) throws SQLException {
String url = "jdbc:postgresql://localhost/test?user=me";
String createTableSql = "CREATE TABLE IF NOT EXISTS dates_and_times (" +
"id SERIAL PRIMARY KEY, " +
"date_time TIMESTAMP WITHOUT TIME ZONE" +
")";
String insertSql = "INSERT INTO dates_and_times(date_time) VALUES(?)";
try (Connection conn = getConnection(url);
Statement createTableStmt = conn.createStatement();
PreparedStatement insertTimestampPs = conn.prepareStatement(insertSql)
) {
createTableStmt.execute(createTableSql);
TimeZone.setDefault(TimeZone.getTimeZone("Europe/Paris"));
// This time did not exist in the Europe/Paris time zone because it
// fell into the hour skipped when Paris transitioned from Central
// European Time to Central European Summer Time
LocalDateTime localDateTime = LocalDateTime.parse("2020-03-29T02:30");
insertTimestampPs.setObject(1, localDateTime);
insertTimestampPs.executeUpdate();
// If we go and look in the database now, the timestamp will be
// 2020-03-29 03:30:00 rather than the expected 2020-03-29 02:30:00
}
}
```
We can see how this happens in the code: https://github.com/pgjdbc/pgjdbc/blob/93d8b68d61c125a94ecb9b167c5c343045fd455b/pgjdbc/src/main/java/...
We start off with a `LocalDateTime` of `2020-03-29T02:30`. This then gets combined with the default time zone (Europe/Paris, in this case) to create a `ZonedDateTime`. Since there was no such time as `2020-03-29T02:30` in the Europe/Paris time zone, it gets adjusted and we end up with a `ZonedDateTime` of `2020-03-29T03:30+02:00[Europe/Paris]`. This then gets converted to an `OffsetDateTime` of `2020-03-29T03:30+02:00`. After a bit more finessing, it gets passed to the backend, where I understand the offset gets dropped because it’s a `TIMESTAMP WITHOUT TIME ZONE` column, leaving just `2020-03-29 03:30:00` to be stored.
This only happens during that hour. If our `LocalDateTime` was an hour earlier, then `2020-03-29T01:30` would become `2020-03-29T01:30+01:00[Europe/Paris]`, then `2020-03-29T01:30+01:00` and eventually `2020-03-29 01:30:00`.
I know JDBC requires that the default time zone be used whenever one is required but I don’t think that applies in this case. Both `LocalDateTime` and `TIMESTAMP WITHOUT TIME ZONE` represent year-month-day-hour-minute-second values so a time zone should not be required to convert between one and the other. The fact that an offset has to be passed to the backend is really just an implementation detail of Postgres.
If UTC was always used for the offset, this would solve the problem because UTC always has a consistent offset, so every `LocalDateTime` can be converted to an `ZonedDateTime` without the local time changing. However, the comments on https://github.com/pgjdbc/pgjdbc/pull/1391 indicate there’s a bit more to it than that. I think this would also change how a `LocalDateTime` gets stored in a `TIMESTAMP WITH TIME ZONE` column (not part of the JDBC spec as far as I’m aware but it does seem to be supported).
The Java time API can always provide an offset for a `LocalDateTime` in a given time zone, even if that time did not exist (so for `2020-03-29T02:30` combined with Europe/Paris, it gives +01:00). This means it might be possible to fix this while still using the default time zone by skipping the intermediate `ZonedDateTime` and going straight to the `OffsetDateTime`. Something like this:
```java
java.time.ZoneOffset zoneOffset = getDefaultTz().toZoneId().getRules().getOffset(localDateTime);
return toString(localDateTime.atOffset(zoneOffset));
```
I think this would work for putting a `LocalDateTime` in a `TIMESTAMP WITHOUT TIME ZONE` column (because the offset is always thrown away) but I’m not certain about the other cases that use this code.
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: github://pgjdbc/pgjdbc
Cc: [email protected], [email protected]
Subject: Re: [pgjdbc/pgjdbc] issue #1390: LocalDateTime has invalid string serialization
In-Reply-To: <<[email protected]>>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox