Re: surprising to_timestamp behavior

On Tue, Oct 29, 2013 at 11:05 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Oct 29, 2013 at 12:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > 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.
>
> Well, you're the second person to react that way to this proposal, but
> the current behavior seems mighty odd to me - even odder, now that I
> realize that we'll happily match '"cat'" to 'dog'. I just work here,
> though.
>

Well, I agree with Tom that user provided two spaces to skip before hours
and this is what we are exactly doing.

Still here are few other observations:

(1) I don't see following as wrong output in postgresql as I already said
above and agreed with Tom. (in input, only one space between DD and HH24,
but
in mask we have 2 spaces)

postgres=# select to_timestamp('2011-03-18 23:38:15', 'YYYY-MM-DD
HH24:MI:SS');
to_timestamp
---------------------------
2011-03-18 03:38:15+05:30
(1 row)

(Note that, time 23 became 03, due to extra space in mask eating 2 in 23,
resulting in 3 for HH24. But fair enough, as expected and thus NO issues)

(2) But I see following buggy (both in input and mask we have 2 spaces
between DD and HH24)

postgres=# select to_timestamp('2011-03-18 23:38:15', 'YYYY-MM-DD
HH24:MI:SS');
to_timestamp
---------------------------
2011-03-18 03:38:15+05:30
(1 row)

(Note that, this time we should not end up with eating 2 from 23 as we have
exact spaces in mask and input. NOT so good and NOT expected, looks like
BUG)

So I think we need to resolve second case.

Thanks

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company