Converting VARCHAR to TIMESTAMP with milleseconds yields "ERROR: date/time field value out of range"

Question

I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command:

select last_run, 
       to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - interval '2 hour','yyyy-MM-dd"T"HH24:mi:ss"Z"') as new_last_run 
from schedule_tasks;

Until the version 9.6.10 of PostgreSQL, all works good, but now I will migrate to 10.5 and the same command returns ERROR. The same issue happens on PostgreSQL 11.

What I realize is that, on 9.6, the first value of milliseconds when I have 4 digits is added to seconds; for example, 2018-07-05T16:14:27.1427 converted is 2018-07-05T16:14:28.427.

I don't know if this behavior is normal in the newer versions and I will have to handle it, or if this is a bug.

Answer 1

Handling of date and timestamp input has been made stricter in Postgres 10. Quoting the release notes:

Make to_timestamp() and to_date() reject out-of-range input fields (Artur Zakirov)

For example, previously to_date('2009-06-40','YYYY-MM-DD') was accepted and returned 2009-07-10. It will now generate an error.

Explains what you observed. You'll need to sanitize your input.

Here is the thread where these changes were discussed.

Answer 2

Casting seems to work fine with PostgreSQL 11.5

select '2018-07-05T16:14:27.1427', 
       to_char(to_timestamp('2018-07-05T16:14:27.1427','yyyy-MM-dd HH24:mi:ss') - interval '2 hour','yyyy-MM-dd"T"HH24:mi:ss"Z"') as new_last_run