Re: surprising to_timestamp behavior

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> It turns out that when you use the to_timestamp function, a space in
> the format mask can result in skipping any character at all, even a
> digit, in the input string. Consider this example, where 10 hours are
> lost:

> rhaas=# select to_timestamp('2013-10-29 10:47:18', 'YYYY-MM-DD HH24:MI:SS');
> to_timestamp
> ------------------------
> 2013-10-29 00:47:18-04
> (1 row)

And that's a bug why? The format says to ignore two characters before the
hours field. I think you're proposing to remove important functionality.

To refine the point a bit, it's absolutely stupid to be using to_timestamp
at all for sane input data like this example. Just cast the string to
timestamp(tz), and the standard datatype input function will do a better
job than to_timestamp ever would. The point of to_timestamp, IMNSHO,
is to extract data successfully from weirdly formatted input; which might
well include cases where there are stray digits you don't want taken as
data. So I'm not on board with proposals to "fix" cases like this by
making the format string's meaning squishier.

regards, tom lane