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)
There's already some code in the relevant function (DCH_from_char) to
skip whitespace characters, but that happens *in addition* to the
mandatory one-character skip. I suggest that we revamp the logic so
that we *either* skip whitespace *or* skip exactly one character from
the input string - but not both. That fixes this case, because the
first space in the mask matches the input space, and the second one is
allowed to match nothing instead of being forced to consume the "1"
following the space. See attached patch.
One problem with this is that it breaks the regression tests. Right
now, this works:
SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
'"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
First, the quoted string matches exactly. Next, the subsequent space
in the format mask skips the colon in the input. And now it's trying
to match YYYY against " 1976", and it's happy to ignore that leading
space as part of decoding a numeric field. With the proposed patch,
we're no longer willing to match the colon in the input to the space
in the format mask, so it errors out, complaining that the colon
doesn't look like a number. I tend to think that's more correct than
what we're doing now.
The other regression test that fails is:
SELECT to_timestamp('15 "text between quote marks" 98 54 45',
E'HH24 "\\text between quote marks\\"" YY MI SS');
With the patch, this fails with:
ERROR: invalid value "rk" for "YY"
The problem is basically that we're quite happy to skip quoted text
that doesn't match at all, as long as it's the same length. And the
format string has extra cruft in it, so they're not, and we end up in
the wrong spot. Either I'm missing something, or our current behavior
here is nothing short of bizarre.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company