pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedsetting the timezone parameter with space cause diff result
5+ messages / 3 participants
[nested] [flat]
* setting the timezone parameter with space cause diff result
@ 2021-06-08 08:17 甄明洋 <zhenmingyang@yeah.net>
2021-06-08 18:02 ` Re: setting the timezone parameter with space cause diff result Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: 甄明洋 @ 2021-06-08 08:17 UTC (permalink / raw)
To: pgsql-bugs
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=#
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: setting the timezone parameter with space cause diff result
2021-06-08 08:17 setting the timezone parameter with space cause diff result 甄明洋 <zhenmingyang@yeah.net>
@ 2021-06-08 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-09 02:27 ` Re:Re: setting the timezone parameter with space cause diff result 甄明洋 <zhenmingyang@yeah.net>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2021-06-08 18:02 UTC (permalink / raw)
To: 甄明洋 <zhenmingyang@yeah.net>; +Cc: pgsql-bugs
=?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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:Re: setting the timezone parameter with space cause diff result
2021-06-08 08:17 setting the timezone parameter with space cause diff result 甄明洋 <zhenmingyang@yeah.net>
2021-06-08 18:02 ` Re: setting the timezone parameter with space cause diff result Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-06-09 02:27 ` 甄明洋 <zhenmingyang@yeah.net>
2021-06-09 02:46 ` Re: setting the timezone parameter with space cause diff result David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: 甄明洋 @ 2021-06-09 02:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: setting the timezone parameter with space cause diff result
2021-06-08 08:17 setting the timezone parameter with space cause diff result 甄明洋 <zhenmingyang@yeah.net>
2021-06-08 18:02 ` Re: setting the timezone parameter with space cause diff result Tom Lane <tgl@sss.pgh.pa.us>
2021-06-09 02:27 ` Re:Re: setting the timezone parameter with space cause diff result 甄明洋 <zhenmingyang@yeah.net>
@ 2021-06-09 02:46 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-09 03:20 ` Re: setting the timezone parameter with space cause diff result Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-06-09 02:46 UTC (permalink / raw)
To: 甄明洋 <zhenmingyang@yeah.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: setting the timezone parameter with space cause diff result
2021-06-08 08:17 setting the timezone parameter with space cause diff result 甄明洋 <zhenmingyang@yeah.net>
2021-06-08 18:02 ` Re: setting the timezone parameter with space cause diff result Tom Lane <tgl@sss.pgh.pa.us>
2021-06-09 02:27 ` Re:Re: setting the timezone parameter with space cause diff result 甄明洋 <zhenmingyang@yeah.net>
2021-06-09 02:46 ` Re: setting the timezone parameter with space cause diff result David G. Johnston <david.g.johnston@gmail.com>
@ 2021-06-09 03:20 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2021-06-09 03:20 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: 甄明洋 <zhenmingyang@yeah.net>; pgsql-bugs
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-06-09 03:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-08 08:17 setting the timezone parameter with space cause diff result 甄明洋 <zhenmingyang@yeah.net>
2021-06-08 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-09 02:27 ` 甄明洋 <zhenmingyang@yeah.net>
2021-06-09 02:46 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-09 03:20 ` Tom Lane <tgl@sss.pgh.pa.us>
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