From: 甄明洋 <zhenmingyang@yeah.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@postgresql.org
Subject: Re:Re: setting the timezone parameter with space cause diff result
Date: Wed, 9 Jun 2021 10:27:26 +0800 (CST)
Message-ID: <6229140a.5c5.179ee988fce.Coremail.zhenmingyang@yeah.net> (raw)
In-Reply-To: <1123102.1623175370@sss.pgh.pa.us>
References: <37c04704.1327.179eab261fd.Coremail.zhenmingyang@yeah.net>
<1123102.1623175370@sss.pgh.pa.us>
Thank you very much for your reply, i'am clear now.
Here is an example, which seems to be a problem of parsing, if format string with FF and TZH:TZM, but the datetime string without fractional seconds will cause parsing misalignment，example：
postgres=# set timezone='-04:00';
SET
postgres=#
/* datetime string with fractional second */
postgres=# select to_timestamp('2021-6-9 10:30:30.369 +04:00', 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM');
to_timestamp
----------------------------
2021-06-09 10:30:30.369+04
(1 row)
postgres=#
/* datetime string without fractional second */
postgres=# select to_timestamp('2021-6-9 10:30:30 +04:00', 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM');
to_timestamp
---------------------------
2021-06-09 14:30:30.04+04
(1 row)
postgres=#
At 2021-06-09 02:02:50, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>=?UTF-8?B?55SE5piO5rSL?= <zhenmingyang@yeah.net> writes:
>> /* timezone string with space */
>> postgres=# set timezone=' +04:00 ';
>> SET
>> postgres=# select now();
>> now
>> -------------------------------
>> 2021-06-08 05:14:36.486693-03
>> (1 row)
>
>What is happening here is that the setting is being interpreted
>much like 'X+04:00Y'. That is, it's taken as a POSIX timezone
>specifier with standard-time abbreviation being ' ', daylight-
>savings abbreviation also being ' ', and the daylight-savings
>offset and transition rules all being defaulted. Your example
>without any spaces is recognized as a POSIX timezone spec
>with no DST part, so that's why it behaves differently.
>
>There's certainly room to quibble about whether a single space
>ought to be considered a valid zone abbreviation. However,
>this behavior comes directly from the IANA tzcode library,
>so I'm hesitant to change it.
>
> regards, tom lane
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: pgsql-bugs@postgresql.org
Cc: zhenmingyang@yeah.net, tgl@sss.pgh.pa.us
Subject: Re:Re: setting the timezone parameter with space cause diff result
In-Reply-To: <6229140a.5c5.179ee988fce.Coremail.zhenmingyang@yeah.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox