LocalDateTime conversion to timestamp without time zone is affected by JVM timezone at DST boundary

[chadshowalter]

Description
When using a java LocalDateTime set to the DST transition hour in the JVM's timezone as a parameter in a query, the value received by PostgreSQL is shifted by one hour.

Driver Version
42.5.4
Java Version
OpenJDK 64-Bit Server VM Corretto-17.0.6.10.1
OS Version
OS X Ventura 13.2.1 (also observed on Ubuntu 16.04.5 LTS)
PostgreSQL Version
PostgreSQL 13.8
To Reproduce

1. Set system time to America/New_York
2. Run the following code

public class TestDSTBug {
    public static void main(String []args) throws Exception {

        String url = "jdbc:postgresql://localhost:5432/test";

        Properties props = new Properties();
        props.setProperty("user", "test");
        props.setProperty("password", "test");
        LocalDateTime ts = LocalDateTime.of(2023,3,12,2,0); //DST boundary in Eastern Time Zone
        try ( Connection conn = DriverManager.getConnection(url, props) ){
            try ( PreparedStatement statement = conn.prepareStatement("select * from (values(?::timestamp)) as v(ts)")) {
                statement.setObject(1, ts);
                try (ResultSet rs = statement.executeQuery( )){
                    if (rs.next())
                        System.out.println("before, after (jdbc): " + ts + ", " + rs.getObject(1, LocalDateTime.class));
                }
            }
        }
    }
}

Result:

//results: before, after (jdbc): 2023-03-12T02:00, 2023-03-12T03:00

Expected behaviour
I expected no shift, that is:

//results: before, after (jdbc): 2023-03-12T02:00, 2023-03-12T02:00

Note that the results are different (no shift) if the JVM's timezone is one that does not transition to Daylight Savings Time, e.g., UTC or Hawaii. Also note that the results are not shifted if the parameter type is String, regardless of JVM time zone.

[davecramer]

Here

pgjdbc/pgjdbc/src/main/java/org/postgresql/jdbc/TimestampUtils.java

Line 972 in 051ae1b

ZonedDateTime zonedDateTime = localDateTime.atZone(getDefaultTz().toZoneId());

is where the problem lies.

Because you cast it to timestamp you are losing the timezone information.

The driver is sending 2023-03-12T03:00-04 which is correct however
by casting to timestamp postgres does

select '2023-03-12T03:00-04'::timestamp;
      timestamp
---------------------
 2023-03-12 03:00:00

as opposed to

postgres=# select '2023-03-12T03:00-04'::timestamptz;
      timestamptz
------------------------
 2023-03-12 03:00:00-04
(1 row)

or 
postgres=# select '2023-03-12T02:00-05'::timestamptz;
      timestamptz
------------------------
 2023-03-12 03:00:00-04

[vlsi]

I guess we should consider passing LocalDateTime as timestamp, not as strings.

WDYT?

[davecramer]

I don't think it changes anything. Postgres is the one removing the timezone

[vlsi]

Suppose LocalDateTime.of(2023,3,12,2,0) sends 2023-03-12T02:00 to the backend (without the time zone). Then it should probably be able to keep the timestamp intact.

[davecramer]

well generally if the target column is a timestamp sending timestamptz to it does the right thing, however in this case the user has made the decision to cast it to a timestamp.

create temp table ts( ts timestamp, tsz timestamptz);
insert into ts values ('2023-03-12T02:00-05', '2023-03-12T02:00-05');
INSERT 0 1
test=# table ts;
         ts          |          tsz
---------------------+------------------------
 2023-03-12 02:00:00 | 2023-03-12 03:00:00-04

[chadshowalter]

The cast is not actually necessary. I believe the behavior is inconsistent. Let me illustrate another way.

create table test(ts timestamp);

Then,

LocalDateTime ts = LocalDateTime.of(2023,3,12,1,0); //still in standard time in Eastern time zone (before the DST transition)
try ( Connection conn = DriverManager.getConnection(url, props) ){
    try ( PreparedStatement statement = conn.prepareStatement("insert into test(ts) values(?) returning ts")) {
        statement.setObject(1, ts);
        try (ResultSet rs = statement.executeQuery( )){
            if (rs.next())
                System.out.println("before, after (jdbc): " + ts + ", " + rs.getObject(1, LocalDateTime.class));
        }
    }
} 
//before, after (jdbc): 2023-03-12T01:00, 2023-03-12T01:00

inspect the table directly:

test=# select * from test;
         ts
---------------------
 2023-03-12 01:00:00
(1 row)

So in this case, my JVM timezone is not used in any conversion (which was both my intent, and my understanding from the docs) and the timestamp without time zone column holds a value that is 2023-03-12 01:00:00Z.

However, if I repeat the experiment above with

LocalDateTime ts = LocalDateTime.of(2023,3,12,2,0); //DST boundary in America/New_York time zone

I get

         ts
---------------------
 2023-03-12 03:00:00
(1 row)

I don't see how this can be the correct behavior.

[chadshowalter]

Also, @davecramer, I am confused by your observation that if I instead send a timestamptz to a timestamp column, I get the correct behavior. It is indeed the case, as you illustrated, that '2023-03-12T02:00-05' (timestamptz) becomes 2023-03-12 02:00:00 (timestamp). However, I understand that the supported mapping to/from Java types is LocalDateTime.java <-> timestamp [without time zone] and OffsetDateTime.java <-> timestamptz. It seems then that I should get consistent behavior with the mapping I used in my illustration.

[davecramer]

I haven't checked but I think if you store it in the database it will be correct. Using it in the query as you did however seems not to work

[bokken]

Passing LocalDateTime as a timestamp makes great sense IF the column is a timestamp. It opens a murky passivity issue if the column type is timestamptz. The latter would shift to using the database’s timezone rather than clients, right?
Representing times which never existed in the actual local time zone of client or server is likely always going to be somewhat tricky.

[vlsi]

If the user code uses LocalDateTime, then they pretty much mean timestamp without time zone

[vlsi]

LocalDateTime handling has been broken since 42.2.0: be06946#diff-21e3023c84d9d5fb4f5f04678a094fce6ab5af36a5e73fdfaf495646f3b720d5R776-R778

[vlsi]

The culprit is that ResultSet#getString calls PgResultSet#internalGetObject which calls getTimestamp for case Types.TIMESTAMP. Apparently, getTimestap returns Timestamp, and it can't represent 2023-03-12T02:00 in America/New_York, because, well, java.sql.Timestamp#toString() always normalizes according to the default time zone.

The question basically boils down to the fact that PgResultSet#getObject(...) would return java.sql.Timestamp for TIMESTAMP type, and there's non-zero desire to keep rs.getObject(1).toString() consistent with rs.getString(1).

So the ways out are:

1. We should revert the change regarding toString(LocalDateTime localDateTime), so it no longer converts LocalDateTime to ZonedDateTime
2. Then we get broken tests since they perform rs.getString(1) to verify the "actual" results:

   pgjdbc/pgjdbc/src/test/java/org/postgresql/test/jdbc42/SetObject310Test.java

   Line 141 in 69818fb

   return readString(columnName);

   
   However, getObject(.., LocalDateTime.class) works properly, and it does not apply the timezones, so if we use it for testing LocalDateTIme, then, well it should work
3. There will be an open question with getString(int | string) and getObject(int | string) which internally use java.sql.Timestamp, so they can't really represent some of the dates. It is tempting to start returning LocalDateTime from getObject(), however, I am afraid it would break many applications, so it is probably not an option :-/
   Of course, we can try adding option like useJavaTimeTypesFromGetObject, however, it is not clear what would be the use for that property as the application that know of LocalDateTime can use getObject(..., LocalDateTime). The only difference would be like getString() would handle edge cases slightly better at a cost of breaking apps that expected did not expect LocalDateTime to appear out of getObject().

Any thoughts on that?

[vlsi]

This issue duplicates #1390