setting the timezone parameter with space cause diff result

=?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:

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