Обсуждение: setting the timezone parameter with space cause diff result
steps ro reproduce:
postgres=# set timezone='+04:00';
SET
postgres=# select now();
now
-------------------------------
2021-06-08 04:14:26.994218-04
(1 row)
/* timezone string with space */
postgres=# set timezone=' +04:00 ';
SET
postgres=# select now();
now
-------------------------------
2021-06-08 05:14:36.486693-03
(1 row)
postgres=#
=?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
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: >甄明洋 <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
On Tuesday, June 8, 2021, 甄明洋 <zhenmingyang@yeah.net> wrote:
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，
Yes, with a template language this complex it is possible that invalid input will mis-parse instead of provoking an error. Use regexes if you need to validate the input format - all the parser should really be relied upon to do is take a valid input and convert it.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, June 8, 2021, 甄明洋 <zhenmingyang@yeah.net> wrote:
>> 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，
> Yes, with a template language this complex it is possible that invalid
> input will mis-parse instead of provoking an error. Use regexes if you
> need to validate the input format - all the parser should really be relied
> upon to do is take a valid input and convert it.
TBH, if we simply took away to_timestamp(), most users would be better off.
I do not think I've ever seen a single complaint about it that didn't
involve an input format that timestamp_in doesn't handle as well or
better.
to_timestamp with a normal-looking format is an antipattern. Just cast
the string to timestamp(tz), instead. Consider to_timestamp *only*
after you find that doesn't work.
regards, tom lane