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.

  • Can you have 1427 milliseconds? – Gerard H. Pille Oct 9 '18 at 10:05
  • Casting seems to work just fine: '2018-07-05T16:14:27.1427'::timestamp But to_timestamp() should be told, that there are microseconds: to_timestamp(last_run, 'yyyy-mm-dd hh24:mi:ss.US') – a_horse_with_no_name Oct 9 '18 at 10:16
  • 2
    But the real question is: why are you storing timestamps in a varchar column? If you had chose the correct data type you would not have that problem now. – a_horse_with_no_name Oct 9 '18 at 10:17
  • So do you have your answer? – Erwin Brandstetter Nov 30 '18 at 20:46
  • Yes. And the Dev will change the query to compatible with the version 10. – Cleiton Domazak Dec 10 '18 at 15:19

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.


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 
  • 1
    No, this produces ERROR: invalid value "T1" for "HH24". – Laurenz Albe Oct 10 '19 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.