Thread: Startup parameters timezone conversion
Hi all!
This is implementation of the method ConnectionFactoryImpl.createPostgresTimeZone() which has the comment:" Convert Java time zone to postgres time zone. All others stay the same except that GMT+n changes to GMT-nn and vise versa."
I'm confused with that changing. Can anybody talk about the reasons and motives of this behavior?
BR, Leonid Vygovskiy
I have to admit to being unable to explain this as well.
Dave Cramer
www.postgres.rocks
On Wed, 19 Jan 2022 at 08:22, Leonid Vygovskiy <Leonid.Vygovskiy@gmail.com> wrote:
Hi all!I study how to work with the timezone in JDBC driver and found the code which I don't understand.This is implementation of the method ConnectionFactoryImpl.createPostgresTimeZone() which has the comment:" Convert Java time zone to postgres time zone. All others stay the same except that GMT+n changes to GMT-nn and vise versa."I'm confused with that changing. Can anybody talk about the reasons and motives of this behavior?BR, Leonid Vygovskiy
Could this be related to Posix time zones? https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html > the positive sign is used for zones west of Greenwich. (Note that this is the opposite of the ISO-8601 sign conventionused elsewhere in PostgreSQL Thomas Dave Cramer schrieb am 19.01.2022 um 15:01: > I have to admit to being unable to explain this as well. > > > Dave Cramer > www.postgres.rocks > > > On Wed, 19 Jan 2022 at 08:22, Leonid Vygovskiy <Leonid.Vygovskiy@gmail.com <mailto:Leonid.Vygovskiy@gmail.com>> wrote: > > Hi all! > > I study how to work with the timezone in JDBC driver and found the code which I don't understand. > This is implementation of the method ConnectionFactoryImpl.createPostgresTimeZone() which has the comment:" ConvertJava time zone to postgres time zone. All others stay the same except that GMT+n changes to GMT-nn and vise versa." > > I'm confused with that changing. Can anybody talk about the reasons and motives of this behavior? > > BR, Leonid Vygovskiy >
On Wednesday, January 19, 2022, Thomas Kellerer <shammat@gmx.net> wrote:
Could this be related to Posix time zones?
https://www.postgresql.org/docs/current/datetime-posix- timezone-specs.html
> the positive sign is used for zones west of Greenwich. (Note that this is the opposite of the ISO-8601 sign convention used elsewhere in PostgreSQL
Indeed I presume it is. I wonder whether a carefully chosen timezone specification on the server would cause this to break since the server can be made to report the offset using either convention and so at least for some timezone specifications the flipping of the sign would not be required…
David J.
Leonid Vygovskiy <Leonid.Vygovskiy@gmail.com> writes: > I study how to work with the timezone in JDBC driver and found the code > which I don't understand. > This is implementation of the method > ConnectionFactoryImpl.createPostgresTimeZone() which has the comment:" > Convert Java time zone to postgres time zone. All others stay the same > except that GMT+n changes to GMT-nn and vise versa." > I'm confused with that changing. Can anybody talk about the reasons and > motives of this behavior? I'm guessing that Java thinks the time zone name "GMT+2" means 2 hours east of Greenwich (ISO-8601 sign convention). Postgres thinks it means 2 hours west of Greenwich (POSIX sign convention). See https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html The great thing about standards is there are so many to choose from ;-) regards, tom lane
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Indeed I presume it is. I wonder whether a carefully chosen timezone
> specification on the server would cause this to break since the server can
> be made to report the offset using either convention and so at least for
> some timezone specifications the flipping of the sign would not be required…
AFAIK, "reporting the offset" is always done with the ISO convention.
It's only when trying to interpret a time zone specification that
we consider the POSIX convention (and that's mostly because the
underlying tzdb code does so). This does lead to fun stuff like
postgres=# set timezone = 'GMT+2'; -- read as POSIX zone spec
SET
postgres=# select now();
now
-------------------------------
2022-01-19 13:03:36.000152-02 -- report as ISO
(1 row)
postgres=# set timezone = '+2'; -- read as numeric ISO offset
SET
postgres=# select now();
now
-------------------------------
2022-01-19 17:03:41.722767+02 -- report as ISO
(1 row)
IMO, all these cases are best-avoided legacy conventions.
In practice you should set the timezone using the tzdb zone name
for where you live, e.g. America/New_York.
regards, tom lane
On Wed, 19 Jan 2022 at 09:42, Thomas Kellerer <shammat@gmx.net> wrote:
Could this be related to Posix time zones?
https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html
> the positive sign is used for zones west of Greenwich. (Note that this is the opposite of the ISO-8601 sign convention used elsewhere in PostgreSQL
Thomas
Yes this is the case
test=# set timezone to 'GMT-5';
SET
test=# select now();
now
-------------------------------
2022-01-19 20:05:25.515391+05
(1 row)
test=# set timezone to 'GMT+5';
SET
test=# select now();
now
-------------------------------
2022-01-19 10:05:52.054867-05
(1 row)
On Wed, 19 Jan 2022 at 10:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Indeed I presume it is. I wonder whether a carefully chosen timezone
> specification on the server would cause this to break since the server can
> be made to report the offset using either convention and so at least for
> some timezone specifications the flipping of the sign would not be required…
AFAIK, "reporting the offset" is always done with the ISO convention.
It's only when trying to interpret a time zone specification that
we consider the POSIX convention (and that's mostly because the
underlying tzdb code does so). This does lead to fun stuff like
postgres=# set timezone = 'GMT+2'; -- read as POSIX zone spec
SET
postgres=# select now();
now
-------------------------------
2022-01-19 13:03:36.000152-02 -- report as ISO
(1 row)
postgres=# set timezone = '+2'; -- read as numeric ISO offset
SET
postgres=# select now();
now
-------------------------------
2022-01-19 17:03:41.722767+02 -- report as ISO
(1 row)
IMO, all these cases are best-avoided legacy conventions.
In practice you should set the timezone using the tzdb zone name
for where you live, e.g. America/New_York.
regards, tom lane
Thanks Tom
We don't really deal with +2 without GMT in java so this isn't an issue. The code really only switches POSIX to ISO
I'll add a comment to the code.
Man I love API's
Dave