On Tue, Sep 18, 2018 at 2:08 PM Prabhat Sahu
<prabhat.sahu@enterprisedb.com> wrote:
>
> Few more findings on to_timestamp() test with HEAD.
>
> postgres[3493]=# select to_timestamp('15-07-1984 23:30:32',' dd- mm- yyyy hh24: mi: ss');
> to_timestamp
> ---------------------------
> 1984-07-15 23:30:32+05:30
> (1 row)
>
> postgres[3493]=# select to_timestamp('15-07-1984 23:30:32','9dd-9mm-99yyyy 9hh24:9mi:9ss');
> to_timestamp
> ------------------------------
> 0084-07-05 03:00:02+05:53:28
> (1 row)
>
> If there are spaces before any formate then output is fine(1st output) but instead of spaces if we have digit then we
aregetting wrong output.
This behavior might look strange, but it wasn't introduced by
cf9846724. to_timestamp() behaves so, because it takes digit have
NODE_TYPE_CHAR type. And for NODE_TYPE_CHAR we just "eat" single
character of input string regardless what is it.
But, I found related issue in cf9846724. Before it was:
# select to_timestamp('2018 01 01', 'YYYY9MM9DD');
to_timestamp
------------------------
2018-01-01 00:00:00+03
(1 row)
But after it becomes so.
# select to_timestamp('2018 01 01', 'YYYY9MM9DD');
ERROR: invalid value "1 " for "MM"
DETAIL: Field requires 2 characters, but only 1 could be parsed.
HINT: If your source string is not fixed-width, try using the "FM" modifier.
That happens because we've already skipped space "for free", and then
NODE_TYPE_CHAR eats digit. I've checked that Oracle doesn't allow
random charaters/digits to appear in format string.
select to_timestamp('2018 01 01', 'YYYY9MM9DD') from dual
ORA-01821: date format not recognized
So, Oracle compatibility isn't argument here. Therefore I'm going to
propose following fix for that: let NODE_TYPE_CHAR eat characters only
if we didn't skip input string characters more than it was in format
string. I'm sorry for vague explanation. I'll come up with patch
later, and it should be clear then.
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company