pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: vlsi (@vlsi) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: Re: [pgjdbc/pgjdbc] PR #3531: Client default timezone no longer sent to the server
Date: Thu, 20 Feb 2025 08:00:33 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
The test effectively verifies if `insert into test(dt) values('1950-02-07')` would allow client to read the value via `ResultSet#getDate()` and compare it with `new Date(y - 1900, m - 1, d )`.
The thing is "plain text inserts to timestamp-typed columns" would use `TimeZone` connection parameter.
The test verifies that "string-based insert" would use the same time zone as `new Date(...)`.
`new Date()` uses JVM default time zone, while `values('1950...` uses connection' zone.
The test verifies both to agree.
Frankly, if we drop `TimeZone` connection parameter, we would break this behaviour which might indeed be used in quite a few of places.
The sad point is we can't detect "bad usages" from the driver's perspective, and we can't even issue warnings to the users "oh, you send dates in a text form, please note you might get different behaviour now".
Even though it might sound dumb or strange to send timestamps as text, I would say it is pretty much fine to expect that `setObejct(, LocalDate.of(y, m, d)` should produce the same values as `setObject(, "y-m-d")`.
---
At the same time, I was in a position when I was unable to connect to an Oracle Database due to exactly the same issue: my machine was up-to-date while the server was using an outdated OS/DB. The outdated OS/DB was to test compatibility with older DB versions which was hard to patch.
---
Frankly, I see two ways out:
a) Catch `invalid TimeZone` at the startup, and retry the connection with `GMT-...` style time zone. In other words, if the user has a fancy-named default time zone, we could extract GMT offset and use it. The drawback is that DST changes would not be reflected, so if the user acquires the connection somewhere near the DST change, they might get slightly different outputs as DST changes. Of course, we could add a job that would refresh `TimeZone` as DST changes, however, it would be quite something to implement.
At the same time, this solution sounds like the one that both addresses "can't connect" issue and keeps the backward compatibility
b) We could catch the error and convert it to a human-understandable message like "your JVM default time zone is XXX while server does not understand it. Please upgrade the server/os so it recognizes the time zone name or .. or ... otherwise you might get invalid datetime values if inserted via text literals"
c) Make an option that would configure the `TimeZone` setting. I do not think the driver really needs a specific `TimeZone` value, and I think it could operate with any value. However, changing `TimeZone` at might unexpectedly change the app behavior if the application uses string literals to produce timestamps or casts timestamps to text at the server side. Frankly, it would be a shame to have 100500 articles saying `just add TimeZone=UTC to your postgresql connection string`.
view thread (7+ 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] PR #3531: Client default timezone no longer sent to the server
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