How to fix format error defining Trino/Kafka Joda Date Format

I have build a Trino schema for a Kafka stream that needs to parse a date in the following format: 2022-09-19T00:00:00.000+02:00. (The date is Midnight UTC+2h 19th Sept 2022.)

The Schema is here, with the section that it is rejecting highlighted: https://github.com/os-climate/markets-pricing-service/blob/main/config/ecb-trino-schema.yaml#L50-L61

                {
                    "name": "starttime-period",
                    "mapping": "starttime-period",
                    "type": "TIMESTAMP",
                    "dataFormat" :"custom-date-time",
                    "formatHint":"yyyy-mm-dTHH:mm:ss.SSS+HH:mm"
                },
                {
                    "name": "end-time-period",
                    "mapping": "end-time-period",
                    "type": "TIMESTAMP",
                    "dataFormat" :"custom-date-time",
                    "formatHint":"yyyy-mm-dTHH:mm:ss.SSS+HH:mm"
                },

The error is:
SQL Error: Query failed (#20220930_190035_00011_kpnkf): invalid Joda Time pattern 'yyyy-mm-dTHH:mm:ss.SSS+HH:mm' passed as format hint for column 'starttime-period'

I have been through the documentation here: https://trino.io/docs/current/connector/kafka.html and cannot see what I could be doing wrong.

Solution

HH:mm is used to represent hour and minute of the day, +02:00 at the end is a time zone offset, so try to use Z in your format string: "formatHint":"yyyy-mm-dTHH:mm:ss.SSSZ" (joda docs).