Thread: Re: [BUGS] surprising to_timestamp behavior

On Tue, Oct 29, 2013 at 11:05 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Oct 29, 2013 at 12:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.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@postgresql.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

I went to review this, and found that there's not actually a patch
attached ...

                        regards, tom lane