Re: BUG #15141: Faulty ISO 8601 parsing

On Mon, Apr 2, 2018 at 4:31 PM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15141
> Logged by: defanor
> Email address: defanor(at)uberspace(dot)net
> PostgreSQL version: 10.0
> Operating system: Any, apparently
> Description:
>
> The time parsing fails on some valid ISO times, with some locales, e.g.:
>
> # select to_timestamp('2018-04-03T01:45:00,728456785+0000')::timestamp
> with
> time zone;
> ERROR: invalid input syntax for type double precision:
> "2018-04-03T01:45:00,728456785+0000"
> LINE 1: select to_timestamp('
> ​​
> 2018-04-03T01:45:00,728456785+0000')::t...
> ^
>
> Apparently the parsing is locale-dependent (using the locale-dependent
> strtod function), while ISO 8601 permits both comma and full stop, with a
> preference for comma (and without mentioning locales). Would be nice to
> handle both, so that any valid ISO times would get parsed.
>

​The observed problem here is that you've called the single-argument
version of to_timestamp, which takes a double​, and the literal that you've
supplied doesn't look like a double (i.e., it contains hyphens, the letter
T, colons, a comma, and a plus sign). IOW, you've implicitly asked
PostgreSQL to do: "SELECT '
​
2018-04-03T01:45:00,728456785+0000 '::double" and it rightly complains that
it cannot.

If you want to covert a string literal to a timestamp you need to use the
two-argument version of the to_timestamp function and pass a format string
that looks like the ISO 8601 standard.

David J.