sqlpostgresqlamazon-aurorasql-timestampto-timestamp

ERROR: invalid value for "MI" Detail: Value must be an integer

In PostgreSQL, I got an error for the following request:

SELECT TO_TIMESTAMP('2020-03-07T22:34:18Z', 'YYYY-MM-DDTHH24:MI:SSZ');

which yielded:

ERROR: invalid value ":1" for "MI" Detail: Value must be an integer.

Why there would be an error specifically at ":1" and not before?

Solution

  • Postgres 12 works with your query as posted, 9.4 gives the error you state because it is interpreting DDTH as "day with letters after" like 1st, 2nd, 3rd, 4th i.e. Nth

    So.. the parser will consume the day 07 and then the next two chars and toss them away (T2 are tossed), then it looks at the next thing in your format string, which is H24: - this is not a recognisable placeholder for anything, so it skips 4 chars from the input too, before it gets to MI which it recognises, but by now :1 from the :18 is in position to be parsed. See the comment below:

    SELECT TO_TIMESTAMP(
  '2020-03-07T22:34:18Z', 
-- YEARsMOsDAYNssssMI
  'YYYY-MM-DDTHH24:MI');

Key: YEAR/MO/DAYN - recognised things
     s - skipped things

    To ignore the T , use a space, not a T literal in the format string:

    SELECT TO_TIMESTAMP('2020-03-07T22:34:18Z', 'YYYY-MM-DD HH24:MI:SS');

    Actually, you can use pretty much anything else that will ordinarily be skipped too