Subject: [pgjdbc/pgjdbc] issue #2311: Why is client time zone not configurable?

From: "riba2101 (@riba2101)" <[email-masked]>
Date: Tue, 19 Oct 2021 09:30:08 +0000

in `org.postgresql.core.v3.ConnectionFactoryImpl#createPostgresTimeZone` the `paramList.add(new String[]{"TimeZone", createPostgresTimeZone()});` is basically hard coded? is there a reason why this is like this?

It could be easily configurable and would not require sessions?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 19 Oct 2021 09:47:00 +0000

Because we set it to the time zone of the JVM. There are quite a few places where we make the assumption that the time zone is the same as the client JVM. What problem are you trying to solve ?

---

From: "riba2101 (@riba2101)" <[email-masked]>
Date: Tue, 19 Oct 2021 12:50:54 +0000

yeah, i saw that.

the problem is that our servers run in UTC, but our devs don't...so everybody needs to configure their JVMs instead simply configuring the connection string

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 19 Oct 2021 12:55:59 +0000

I'm unsure how this effects you. Can you elaborate. If you use timestamp with time zone then everything should "just work"

---

From: "riba2101 (@riba2101)" <[email-masked]>
Date: Wed, 27 Oct 2021 11:38:51 +0000

the "just work" does not apply to `without` zone timestamps

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 27 Oct 2021 11:49:36 +0000

Pretty sure your request would break an awful lot of code. If I understand this correctly you want the connection to have a different timezone than the JVM ? Further since you are using timestamps without a timezone then everything will come back in UTC regardless of what timezone we set the session to. I'm not sure this would solve your problem ?

---

From: "bokken (@bokken)" <[email-masked]>
Date: Wed, 27 Oct 2021 12:30:15 +0000

If you are not using timestamptz, then use the [ResultSet getTimestamp](https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/ResultSet.html#getTimestamp(int,java.util.Calendar)) which takes a `Calendar`. Set the time zone on the calendar to use for interpreting the timestamp.

As @davecramer hinted at, this is all /much/ better if you use timestamptz as the data type.

---

From: "riba2101 (@riba2101)" <[email-masked]>
Date: Wed, 27 Oct 2021 12:53:47 +0000

yeah i kinda think we misunderstood each other.
to change the type on the DB is impossible at this moment. 

my proposal was to have an optional flag for the timezone in the connection string. i kinda fail to understand how this would break anything?

this is the case with the dates being converted into wrong zones when fetched(even as `Timestamp`)

in addition to that i found an deeper problem in psql itself.

`interval +336:00:00` what the driver converts a `Duration` into is DST aware, but `interval + 2 weeks` isn't

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 27 Oct 2021 13:01:24 +0000

Well setting the timezone for the string will have 0 effect on Timestamps without timezones. The server will not change the timezone for those.

```
create table foo (ts timestamp, tz timestamptz);
CREATE TABLE
postgres=# insert into foo values (now(),now());
INSERT 0 1
postgres=# select * from foo;
             ts             |              tz
----------------------------+-------------------------------
 2021-10-27 08:59:21.824902 | 2021-10-27 08:59:21.824902-04
(1 row)

postgres=# set timezone to -5;
SET
postgres=# select * from foo;
             ts             |              tz
----------------------------+-------------------------------
 2021-10-27 08:59:21.824902 | 2021-10-27 07:59:21.824902-05
```

As you can see only the timestamp with time zone changes when I change the session timezone.

I fail to see how setting the session timezone fixes anything for you.

As for your other problem that would be something to discuss with hackers

---

From: "riba2101 (@riba2101)" <[email-masked]>
Date: Wed, 27 Oct 2021 13:23:05 +0000

im not saying that the DB converts it, but rather the driver, because, yet again, the wrong timezone that is not configurable.

the other problem is with psql itself

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 27 Oct 2021 13:24:35 +0000

Setting the session timezone does not change the JVM timezone. I'm confused how you think this would help.

---

From: "riba2101 (@riba2101)" <[email-masked]>
Date: Wed, 27 Oct 2021 13:41:50 +0000

so its absolutely mandatory that the session timezone and jvm timezone are the same?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 27 Oct 2021 13:48:30 +0000

If you are using timestamp without time zone I don't think it matters what you set the session time zone to. The only place it matters is if you are using time and timesatmps with time zone.

---

From: "riba2101 (@riba2101)" <[email-masked]>
Date: Wed, 27 Oct 2021 14:03:44 +0000

that may be, but the question was not about `without timezone` but rather in general :)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 27 Oct 2021 14:07:01 +0000

if they are different and you do not supply a timezone to the getTimestamp then the resulting timestamp will be incorrect.

---

From: "jorsol (@jorsol)" <[email-masked]>
Date: Wed, 27 Oct 2021 15:38:25 +0000

@riba2101 Is there a particular issue you are facing? Do you have some example code that shows the problem? many people use UTC on the servers and a local timezone on their machines without problems.

In Postgres all timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.  So we need to understand what is the real issue that requires a configurable time zone.

---

From: "riba2101 (@riba2101)" <[email-masked]>
Date: Wed, 27 Oct 2021 15:53:20 +0000

@jorsol, @davecramer sry im kinda super busy these days, so the answers were short and most probably lacking info :) sry for that!

the problem is:
* DB not running in UTC(not configurable)
* jvm not running in UTC(lets say not configurable)

timestamp(z)s are converted to local timestamps, since the app kinda doesnt care about any timezones(it passes them simply forward), the session timezone would actually be correct. Now heres the kicker, its impossible to change that except changing the whole jvm, which is super inconvenient for all devs (since devs usually dont have their local machines in UTC). 

so either an optional flag (to mitigate possible timezone issues) to use server timezone or JVM timezone, or a flag to set the session timezone could come in handy here

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 05 Nov 2021 13:07:25 +0000

@riba2101 I'm not inclined to entertain this fix for what is essentially a development environment problem.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 15 Nov 2021 16:32:37 +0000

closed will not fix

---

From: "beanww (@beanww)" <[email-masked]>
Date: Fri, 24 Jan 2025 01:32:55 +0000

I use flink to sync records from postgres to postgres, column typs is timestamptz to timestamptz, 
source connector is flink-sql-connector-postgres-cdc-3.2.1.jar
sink connector is flink-connector-jdbc_2.12-1.14.6.jar
jdbc driver is postgresql-42.7.5.jar
and flink is not support timestamp with time zone in postgres-connector now, so the source->sink type mapping is timestamp,eg:
created_time timestamp
when I do not set flink jvm user.timezone=UTC,  the records syncronized to sink database is wrong, diff is 8 hours that is my OS timezone
So I have 2 question ,  
1. what's difference between flink-sql-connector-postgres-cdc-3.2.1.jar and  flink-connector-jdbc_2.12-1.14.6.jar? if both are pg connection from jdbc, why the timezone is difference?
2. Can this be a reason to add timezone prop from JDBC connection string? just like mysql?
I have tried this method, but sames not working:
jdbc:postgresql://xxx/db?options=-c%20TimeZone%3DUTC

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 24 Jan 2025 12:04:26 +0000

the pgjdbc driver sets the timezone of the session to the clients timezone. This works in 99.999% of cases. Unfortunately, not yours.

There is no option to set the timezone. 

I guess we could add a timezone option. It's really never come up before.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 24 Jan 2025 12:31:16 +0000

@beanww , can flink use `getObject(..., LocalDateTime.class)` instead of `getTimestamp(...)`?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 24 Jan 2025 12:56:12 +0000

@vlsi we would probably have to talk to the @flink project
@beanww can you create an issue there to ask them to use the above?

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Sat, 15 Feb 2025 03:20:36 +0000

Can someone explain why on Earth it is necessary to inform database server about client timezone? What is it used for at the server side? Imagine that my client has timezone Europe/Kurlandia. When it tries to connect to PostgreSQL Server, it gets an error Invalid value for parameter "TimeZone": "Europe/Kurlandia". What exactly does the server fail to do, when it learns that the client has a timezone which is unknown for server's locale libraries?

I do understand how client timezone is used at client side. But I have no idea why database server cares what client timezone is.
Can I just disable those tricky client-timezone-sensitive server features?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 15 Feb 2025 03:24:22 +0000

Simple,

So that timestamp with time zone will be returned in your time zone

Dave Cramer

On Fri, Feb 14, 2025 at 10:20 PM Сергей Б. ***@***.***> wrote:

> Could anybody explain why on Earth it is necessary to inform database
> server about client timezone? What is it used for at the server side?
> Imagine that my client has timezone Europe/Kurlandia. When it tries to
> connect to PostgreSQL Server, it gets an error Invalid value for parameter
> "TimeZone": "Europe/Kurlandia". What exactly does the server fail to do,
> when it learns that the client has a timezone which is unknown for server's
> locale libraries?
>
> I do understand how client timezone is used at client side. But I have no
> idea why database server cares what client timezone is.
> —
> Reply to this email directly, view it on GitHub
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2660690692>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AADDH5TA2NBGI7NAH2GGUAL2P2XBTAVCNFSM6AAAAABVYULISCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRQGY4TANRZGI>
> .
> You are receiving this because you modified the open/close state.Message
> ID: ***@***.***>
> *seregaizsbera* left a comment (pgjdbc/pgjdbc#2311)
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2660690692>
>
> Could anybody explain why on Earth it is necessary to inform database
> server about client timezone? What is it used for at the server side?
> Imagine that my client has timezone Europe/Kurlandia. When it tries to
> connect to PostgreSQL Server, it gets an error Invalid value for parameter
> "TimeZone": "Europe/Kurlandia". What exactly does the server fail to do,
> when it learns that the client has a timezone which is unknown for server's
> locale libraries?
>
> I do understand how client timezone is used at client side. But I have no
> idea why database server cares what client timezone is.
> —
> Reply to this email directly, view it on GitHub
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2660690692>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AADDH5TA2NBGI7NAH2GGUAL2P2XBTAVCNFSM6AAAAABVYULISCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRQGY4TANRZGI>
> .
> You are receiving this because you modified the open/close state.Message
> ID: ***@***.***>
>

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Sat, 15 Feb 2025 03:28:19 +0000

> […](#)

If the value of time is stored with zone, server must send them both as is. Otherwise server must send data in universal representation, and conversion must be performed at client side.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 15 Feb 2025 11:50:26 +0000

Well there's the problem, the server doesn't store the time zone

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Sun, 16 Feb 2025 16:25:51 +0000

Thank you for explanation. This is more than enough for us to make sure once again that we have chosen the right way to work with timestamps. We store all timestamps as long integer number of milliseconds since Unix epoch. No driver can break our data, whatsoever. The only bug we catch sometimes is that our applications fail to connect to the server at some locations. That is why we would appreciate if you implemented an option to specify TimeZone StartupParam.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 16 Feb 2025 20:09:05 +0000

How does that help?

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Sun, 16 Feb 2025 22:17:56 +0000

We'll specify UTC timezone via JDBC URL, and never will run into Invalid value for parameter "TimeZone".

In fact I've already forked sources, and assembled the driver with TimeZone StartupParameter hardcoded as UTC. Everything works just fine. My applications work in their native timezones, and there are no errors nor undesired effects.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 17 Feb 2025 00:49:45 +0000

I still don't understand why you would need this if you don't use time stamps with time zones? There should never be an invalid time zone from the JVM

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Mon, 17 Feb 2025 01:34:19 +0000

I just can't connect to database. Error Invalid value for parameter "TimeZone".

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 17 Feb 2025 03:37:37 +0000

We get the timezone from the JVM. What is the value of the JVM timezone ?

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Mon, 17 Feb 2025 04:04:36 +0000

Timezones are constantly changing. Recent location that caused a failure was America/Ciudad_Juarez.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 17 Feb 2025 11:04:10 +0000

I just tried this timezone on my Mac and it worked perfectly. This timezone
was added in 2022. You need to update your operating system

We will not be adding this parameter

Dave Cramer

On Sun, Feb 16, 2025 at 11:04 PM Сергей Б. ***@***.***> wrote:

> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2661919483>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AADDH5V4P7LILC26ZNRYITT2QFNWTAVCNFSM6AAAAABVYULISCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRRHEYTSNBYGM>
> .
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2661919483>
>
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2661919483>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AADDH5V4P7LILC26ZNRYITT2QFNWTAVCNFSM6AAAAABVYULISCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRRHEYTSNBYGM>
> .

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Tue, 18 Feb 2025 19:51:21 +0000

> We will not be adding this parameter

I'm on the client side. I have no idea what server my users are trying to reach. I'm developing applications to connect to any database. My users' software is up to date.

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Tue, 18 Feb 2025 20:00:59 +0000

> You need to update your operating system

Did I understand correctly that connection with the driver is guaranteed only on clients with Mac OS and the latest version of the operating system at database server?

I suggest adding a description of the operating system requirements on both sides to the documentation.

---

From: "bokken (@bokken)" <[email-masked]>
Date: Tue, 18 Feb 2025 20:20:18 +0000

Is the issue that you are connecting to an old version of the db and/or db
running on old OS which does not recognize a timezone defined in 2022?

On Tue, Feb 18, 2025 at 2:01 PM Сергей Б. ***@***.***> wrote:

> Did I understand correctly that the correct operation of the driver is
> guaranteed only on clients with Mac OS and the latest version of the
> database?
>
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2666814815>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AAW3U3LJWL2I5TZOSISEJDL2QOGRJAVCNFSM6AAAAABVYULISCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRWHAYTIOBRGU>
> .
> You are receiving this because you commented.Message ID:
> ***@***.***>
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2666814815>
>
> Did I understand correctly that the correct operation of the driver is
> guaranteed only on clients with Mac OS and the latest version of the
> database?
>
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2666814815>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AAW3U3LJWL2I5TZOSISEJDL2QOGRJAVCNFSM6AAAAABVYULISCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRWHAYTIOBRGU>
> .
> You are receiving this because you commented.Message ID:
> ***@***.***>
>

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Tue, 18 Feb 2025 20:40:43 +0000

> running on old OS which does not recognize a timezone defined in 2022?

This is not the only server. Users from some locations experience troubles with any postgres database.
Here is an example of the server to which a client failed to connect to (the result of select version()):

```
PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.1 20220127 (Red Hat 11.2.1-1), 64-bit
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Feb 2025 00:41:38 +0000

I'm pretty sure if we allow the user to set the timezone on startup then we would also have to change the JVM timezone to match it otherwise all of the times would be wrong.

---

From: "bokken (@bokken)" <[email-masked]>
Date: Wed, 19 Feb 2025 01:25:02 +0000

Indeed, managing the jvm default timezone might be a better option.

On Tue, Feb 18, 2025 at 6:42 PM Dave Cramer ***@***.***>
wrote:

> I'm pretty sure if we allow the user to set the timezone on startup then
> we would also have to change the JVM timezone to match it otherwise all of
> the times would be wrong.
>
> —
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2667239914>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AAW3U3NEL2SOVZVR4OBE5N32QPHNRAVCNFSM6AAAAABVYULISCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRXGIZTSOJRGQ>
> .
> *davecramer* left a comment (pgjdbc/pgjdbc#2311)
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2667239914>
>
> I'm pretty sure if we allow the user to set the timezone on startup then
> we would also have to change the JVM timezone to match it otherwise all of
> the times would be wrong.
>
> —
> <https://github.com/pgjdbc/pgjdbc/issues/2311#issuecomment-2667239914>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AAW3U3NEL2SOVZVR4OBE5N32QPHNRAVCNFSM6AAAAABVYULISCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMNRXGIZTSOJRGQ>
> .

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Wed, 19 Feb 2025 04:06:05 +0000

> Indeed, managing the jvm default timezone might be a better option.

Could you describe this option in documentation?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Feb 2025 10:58:22 +0000

https://stackoverflow.com/questions/2493749/how-to-set-a-jvm-timezone-properly

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Wed, 19 Feb 2025 13:02:43 +0000

> https://stackoverflow.com/questions/2493749/how-to-set-a-jvm-timezone-properly

This is not a problem to setup a system property in JVM. The problem is when it is necessary to change, and to what value. Because normal programs works correctly with any value of user.timezone, and only pgjdbc doesn't connect to server sometimes. And that is what I ask you to clarify in your documentation.

---

From: "seregaizsbera (@seregaizsbera)" <[email-masked]>
Date: Wed, 19 Feb 2025 13:04:58 +0000

What value of user.timezone is necessary for pgjdbc to work in America/Ciudad_Juarez? And every other places in the Pacific.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 19 Feb 2025 13:18:58 +0000

@seregaizsbera , please tune the tone of your messages.

It would help a lot if you could suggest a PR.
I'm not sure we need a connection property for `TimeZone` parameter, however, at least we should double-check what happens if we just drop sending `TimeZone` as a startup parameter.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Feb 2025 13:39:57 +0000

> What value of user.timezone is necessary for pgjdbc to work in America/Ciudad_Juarez? And every other places in the Pacific.

It would help if you reviewed the server documentation on how timestamps with time zone are handled. The server does not store the timezone. When storing the timestamp it is converted to UTC based on the setting of the timezone. When it is retrieved it is converted back to the setting of the timezone. This is why we set it to the JVM timezone so that all of the time conversions "just work" 

In your case since you are managing your timestamps yourself not setting it or setting it to something different than the JVM works fine. However it won't work fine for everyone else. 

As @vlsi  mentioned create a PR. (You mentioned you have already hacked the driver) and let's see if it passes all of the tests. 

I get that you are frustrated, but as @vlsi mentioned please try working with us. We have a much bigger audience than just you. There are good reasons why just changing something to fix your problem may not be the correct thing to do.

---

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

Task seems to be too complex to be completed by single commit. I suggest we improve the tests a bit first. Please review my PR #3532. @davecramer

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 20 Feb 2025 19:12:40 +0000

So I checked and RPM's will use the system tzdata 
```
rpm/redhat/main/non-common/postgresql-17/main/postgresql-17.spec:       --with-system-tzdata=%{_datadir}/zoneinfo \
```

So if the O/S is upgraded it should work.

---

From: "eirikbakke (@eirikbakke)" <[email-masked]>
Date: Tue, 03 Jun 2025 15:57:15 +0000

The JDBC driver should probably not assume that the client and the server has exactly the same version of the time zone database installed. Often, client applications must be able to connect to servers of many different versions, including servers that depend on older versions of the time zone database. As seen in https://github.com/pgjdbc/pgjdbc/issues/3642 , this can cause connections to fail with the current approach. Developers of client applications often do not have control over the server version and vice versa.

Even when the name of time zones match between client and server, there can be changes in DST transition dates from year to year. If the client and server has a different idea of when DST transitions happen, then off-by-one-hour errors could start appearing silently. This seems like an avoidable problem; since PostgreSQL always stores timestamptz values at UTC, there should really be no need for conversion to happen on the server side at all.

Ideally the JDBC driver would connect with TimeZone=GMT, and perform any necessary conversions on the client side, e.g. when getting a java.sql.Timestamp value from a ResultSet.

(I realize that the newer java.sql.Timestamp.toInstant() API makes all the conversions irrelevant, but obviously there is a lot of older code that uses java.sql.Timestamp directly.)

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 03 Jun 2025 16:26:47 +0000

@eirikbakke , previously, the driver connected with `client` timezone.
If we change the default the `TimeZone=GMT`, it might break applications that might have code like the following:

```
executeQuery("select now()::text")... getString(1)
```

The results would change if the driver silently alters the timezone.

---

Do you have any other suggestion other than "move the driver to TimeZone=GMT by default"?

---

I expect it might be workable if we support `TimeZone` connection parameter so the driver uses the value provided by the user. Then, if user configures `TimeZone=GMT`, it would be user to ensure the queries use `now()::text` properly.

---

From: "eirikbakke (@eirikbakke)" <[email-masked]>
Date: Thu, 05 Jun 2025 01:27:38 +0000

@vlsi That example makes sense; it makes it clear why TimeZone can't be set to GMT by default without breaking backwards compatibility.

Possible solutions (just brainstorming):
1) The already-suggested idea of letting the TimeZone parameter be set to an arbitrary value via a JDBC URL parameter or connection property.
2) Or, a simpler boolean JDBC URL property that _only_ exposes the option of setting TimeZone=GMT, rather than to an arbitrary time zone. But I could imagine cases where someone in Asia/Kolkata would want to connect with TimeZone=Asia/Calcutta to make some existing application "just work", without worrying about potential effects of switching to GMT. So maybe option (1) is better.
3) Or, keep the current behavior, but detect the 'invalid value for parameter "TimeZone"' error from the server and retry the connection automatically with an alternative time zone name for the known special cases (Kiev and Kyiv, Kolkata and Calcutta...).
4) Or, like (3), but connect always with TimeZone=GMT and use "SET SESSION TIME ZONE" to set the time zone instead, catching errors on the latter command. (Avoids re-authenticating etc., but means an extra network round trip in these time zones.)

In any case, it should probably be ensured that ResultSet.getTimestamp does not get affected by any change in the TimeZone parameter.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 05 Jun 2025 09:26:35 +0000

> [@vlsi](https://github.com/vlsi) That example makes sense; it makes it clear why TimeZone can't be set to GMT by default without breaking backwards compatibility.
> 
> 1. The already-suggested idea of letting the TimeZone parameter be set to an arbitrary value via a JDBC URL parameter or connection property.
This is probably the only realistic option.

> 3. Or, a simpler boolean JDBC URL property that _only_ exposes the option of setting TimeZone=GMT, rather than to an arbitrary time zone. But I could imagine cases where someone in Asia/Kolkata would want to connect with TimeZone=Asia/Calcutta to make some existing application "just work", without worrying about potential effects of switching to GMT. So maybe option (1) is better.
> 4. Or, keep the current behavior, but detect the 'invalid value for parameter "TimeZone"' error from the server and retry the connection automatically with an alternative time zone name for the known special cases (Kiev and Kyiv, Kolkata and Calcutta...).

How would we know the correct TZ ?

> 6. Or, like (3), but connect always with TimeZone=GMT and use "SET SESSION TIME ZONE" to set the time zone instead, catching errors on the latter command. (Avoids re-authenticating etc., but means an extra network round trip in these time zones.)> 

. > In any case, it should probably be ensured that ResultSet.getTimestamp does not get affected by any change in the TimeZone parameter.

Well changing the TZ will change the result of getTimestamp. The server changes the time based on the session TZ. It does not store TZ in the data.

---

From: "eirikbakke (@eirikbakke)" <[email-masked]>
Date: Thu, 05 Jun 2025 17:35:50 +0000

> How would we know the correct TZ ?

For that option, the JDBC driver would have to hard-code time zones with known name changes, and try one after the other. (Yes, quite ugly.)

> Well changing the TZ will change the result of getTimestamp. The server changes the time based on the session TZ. It does not store TZ in the data.

The java.sql.Timestamp instance is created by the JDBC driver, no? So if the JDBC driver connects to the server with connection parameter TimeZone=GMT, I'd imagine the JDBC driver would receive the timestamp at GMT/UTC, but then convert it to the local JDK time zone for the purposes of creating the java.sql.Timestamp object (when working with `timestamptz` values). That way, the change to TimeZone=GMT can be regarded as a JDBC driver implementation detail that does not change the semantics of getTimestamp(). (But as @vlsi pointed out it would still change the string representation of `timestamptz` when casted to `text`.)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 05 Jun 2025 18:23:45 +0000

> For that option, the JDBC driver would have to hard-code time zones with known name changes, and try one after the other. (Yes, quite ugly.)
Very ugly
> The java.sql.Timestamp instance is created by the JDBC driver, no? So if the JDBC driver connects to the server with connection parameter TimeZone=GMT, I'd imagine the JDBC driver would receive the timestamp at GMT/UTC, but then convert it to the local JDK time zone for the purposes of creating the java.sql.Timestamp object (when working with `timestamptz` values). That way, the change to TimeZone=GMT can be regarded as a JDBC driver implementation detail that does not change the semantics of getTimestamp(). (But as [@vlsi](https://github.com/vlsi) pointed out it would still change the string representation of `timestamptz` when casted to `text`.)

What happens to data that the driver did not create ?

Dave

---

From: "eirikbakke (@eirikbakke)" <[email-masked]>
Date: Thu, 05 Jun 2025 20:10:49 +0000

> What happens to data that the driver did not create ?

Not sure I understand; could you give an example, please?

-- Eirik

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 05 Jun 2025 20:14:19 +0000

> Not sure I understand; could you give an example, please?

Never mind, what you say will work.