Subject: [pgjdbc/pgjdbc] PR #3531: Client default timezone no longer sent to the server

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Wed, 19 Feb 2025 22:34:18 +0000

Fixes issue #2311

We no longer need to configure timezone startup parameter, because we just don't use it.

### All Submissions:

* [x] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
* [x] Have you checked to ensure there aren't other open [Pull Requests](../../pulls) for the same update/change?

### New Feature Submissions:

1. [x] Does your submission pass tests?
2. [x] Does `./gradlew styleCheck` pass ?
3. [x] Have you added your new test classes to an existing test suite in alphabetical order?

### Changes to Existing Features:

* [ ] Does this break existing behaviour? If so please explain.
* [x] Have you added an explanation of what your changes do and why you'd like us to include them?
* [x] Have you written new tests for your core changes, as applicable?
* [ ] Have you successfully run tests with your changes locally? - Only first 2000 tests. I didn't wait for all the tests to finish because they took too long.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Feb 2025 23:24:07 +0000

So this fails a number of tests.

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Thu, 20 Feb 2025 06:05:57 +0000

Some tests failed. I'm going to send more code fix all tests.

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Thu, 20 Feb 2025 06:09:17 +0000

> So this fails a number of tests.

Yes. Tests took quite a long time to finish.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 20 Feb 2025 08:00:33 +0000

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`.

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Thu, 20 Feb 2025 11:16:15 +0000

Submitted PR #3532 with additional tests.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 21 Feb 2025 14:28:53 +0000

FYI, https://github.com/eggert/tz/blob/main/northamerica contains the timezone in question. I still assert that this is a server mis-configuration. Can you explain why the server has not been updated ?