Re: to_timestamp docs - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: to_timestamp docs
Date
Msg-id 20190501220240.ilbmlc5uypa4j7r6@momjian.us
Whole thread Raw
In response to Re: to_timestamp docs  (Alexander Korotkov <a.korotkov@postgrespro.ru>)
List pgsql-hackers
On Thu, May  2, 2019 at 12:49:23AM +0300, Alexander Korotkov wrote:
> On Wed, May 1, 2019 at 11:20 PM Arthur Zakirov <a.zakirov@postgrespro.ru> wrote:
> > Hello,
> > Not sure if we need some additional checks here if FX is set.
> 
> I'd like to add that this behavior is not new in 12.  It was the same before.

Agreed, but since we are looking at it, let's document it.

> > > It seems DD and YYYY (as numerics?) in FX mode eat trailing whitespace,
> > > while MON does not?  Also, I used these queries to determine it is
> > > "trailing" whitespace that "FXMON" controls:
> > >
> > >         SELECT to_timestamp('JUL   JUL JUL','MON_FXMON_MON');
> > >                   to_timestamp
> > >         ---------------------------------
> > >          0001-07-01 00:00:00-04:56:02 BC
> > >
> > >         SELECT to_timestamp('JUL JUL   JUL','MON_FXMON_MON');
> > >         ERROR:  invalid value "  J" for "MON"
> > >         DETAIL:  The given value did not match any of the allowed values for this field.
> >
> > The problem here is that you need to specify FX only once and at beginning of
> > the format string. It is stated in the documentation:
> >
> > "FX must be specified as the first item in the template."
> >
> > It works globally (but only for remaining string if you don't put it
> > at the beginning)
> > and you can set it only once. For example:
> >
> > =# SELECT to_timestamp('JUL   JUL JUL','FXMON_MON_MON');
> > ERROR:  invalid value "  J" for "MON"
> > DETAIL:  The given value did not match any of the allowed values for this field.
> 
> Actually, FX takes effect on subsequent format patterns.  This is not
> documented, but it copycats Oracle behavior.  Sure, normally FX should
> be specified as the first item.  We could document current behavior or
> restrict specifying FX not as first item.  This is also not new in 12,
> so documenting current behavior is better for compatibility.

Agreed.  Since is it pre-12 behavior, I suggest we just document it and
not change it.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +



pgsql-hackers by date:

Previous
From: Alexander Korotkov
Date:
Subject: Re: to_timestamp docs
Next
From: Arthur Zakirov
Date:
Subject: Re: to_timestamp docs