Message-ID: <gh-pgjdbc-pgjdbc-1390-c698631932@github.com>
From: "alexbishop1 (@alexbishop1)" <noreply+alexbishop1@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Thu, 24 Sep 2020 23:04:43 +0000
Subject: Re: [pgjdbc/pgjdbc] issue #1390: LocalDateTime has invalid string serialization
In-Reply-To: <gh-pgjdbc-pgjdbc-1390@github.com>
References: <gh-pgjdbc-pgjdbc-1390@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: alexbishop1
X-GitHub-Comment-Id: 698631932
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 1390
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/1390#issuecomment-698631932
Content-Type: text/plain; charset=utf-8

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/org/postgresql/jdbc/TimestampUtils.java#L904-L906

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.