Re: to_timestamp docs

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: to_timestamp docs
Date: 2019-05-01 15:04:53
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Wed, May 1, 2019 at 10:01:50AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > I don't think the changes made in PG 12 are documented accurately.
> That code is swapped out of my head at the moment, but it looks
> to me like the para before the one you changed is where we discuss
> the behavior for whitespace. I'm not sure that this change is
> right, or an improvement, in the context of both paras.

Thanks. I think I see the sentence you are thinking of:

<function>to_timestamp</function> and <function>to_date</function>
skip multiple blank spaces at the beginning of the input string
and around date and time values unless the <literal>FX</literal>
option is used.

However, first, it is unclear what 'skip' means here, i.e., does it mean
multiple blank spaces become a single space, or they are ignored. That
should be clarified, though I am unclear if that matters based on how
separators are handled. Also, I think "blank spaces" should be

Second, I see inconsistent behaviour around the use of FX for various
patterns, e.g.:

SELECT to_timestamp('5 1976','FXDD_FXYYYY');
1976-01-05 00:00:00-05

SELECT to_timestamp('JUL JUL','FXMON_FXMON');
0001-07-01 00:00:00-04:56:02 BC

SELECT to_timestamp('JUL JUL','FXMON_FXMON');
ERROR: invalid value " " for "MON"
DETAIL: The given value did not match any of the allowed values for this field.

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:

0001-07-01 00:00:00-04:56:02 BC

ERROR: invalid value " J" for "MON"
DETAIL: The given value did not match any of the allowed values for this field.

Once we figure out how it is behaving I think we can pull together the
FX text above to reference the separator text below.

Bruce Momjian <bruce(at)momjian(dot)us>

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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2019-05-01 15:24:25 Re: Unhappy about API changes in the no-fsm-for-small-rels patch
Previous Message Tomas Vondra 2019-05-01 15:02:20 Re: walsender vs. XLogBackgroundFlush during shutdown