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

user_4756084 0
Viewed 1948

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?

to-timestamp sql-timestamp amazon-aurora postgresql sql
3 Answers

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

user_14106640

Just surround the litteral T with double quotes:

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

Reference: Postgres formatting functions:

In to_date, to_number, and to_timestamp, literal text and double-quoted strings result in skipping the number of characters contained in the string.

user_106767160

Your string is well formatted for a timestamp, so you can just convert it:

select '2020-03-07T22:34:18Z'::timestamp
user_11440350
Related