Re: BUG #15141: Faulty ISO 8601 parsing - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: BUG #15141: Faulty ISO 8601 parsing
Date
Msg-id CAKFQuwYGG9eeZEQoS3cUoiO0-fbfts+JEoDcQqf0SFU++20vTw@mail.gmail.com
Whole thread Raw
In response to BUG #15141: Faulty ISO 8601 parsing  (PG Bug reporting form <noreply@postgresql.org>)
List pgsql-bugs
On Mon, Apr 2, 2018 at 4:31 PM, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      15141
Logged by:          defanor
Email address:      defanor@uberspace.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.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15141: Faulty ISO 8601 parsing
Next
From: Tom Lane
Date:
Subject: Re: BUG #15141: Faulty ISO 8601 parsing