Thread

1. setting the timezone parameter with space cause diff result

   myzhen <zhenmingyang@yeah.net> — 2021-06-08T08:17:09Z

   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=#

2. Re: setting the timezone parameter with space cause diff result

   Tom Lane <tgl@sss.pgh.pa.us> — 2021-06-08T18:02:50Z

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

3. Re:Re: setting the timezone parameter with space cause diff result

   myzhen <zhenmingyang@yeah.net> — 2021-06-09T02:27:26Z

   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
   

4. Re: setting the timezone parameter with space cause diff result

   David G. Johnston <david.g.johnston@gmail.com> — 2021-06-09T02:46:18Z

   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.
   

5. Re: setting the timezone parameter with space cause diff result

   Tom Lane <tgl@sss.pgh.pa.us> — 2021-06-09T03:20:50Z

   "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