I am using a 3rd party application (Debezium Connector). It has to write date time strings in ISO-8601 format into a TIMESTAMPTZ column. Unfortunately this fails, because there is no implicit cast from varchar to timestamp tz.
I did notice that the following works:
SELECT TIMESTAMPTZ('2021-01-05T05:17:46Z'); SELECT TIMESTAMPTZ('2021-01-05T05:17:46.123Z');
I tried the following:
- Create a function and a cast
CREATE OR REPLACE FUNCTION varchar_to_timestamptz(val VARCHAR) RETURNS timestamptz AS $ $ SELECT TIMESTAMPTZ(val) INTO tstz; $ $ LANGUAGE SQL; CREATE CAST (varchar as timestamptz) WITH FUNCTION varchar_to_timestamptz (varchar) AS IMPLICIT;
Unfortunately, it gives the following errors:
function timestamptz(character varying) does not exist
I also tried the same as above but using plpgsql and got the same error.
I tried writing a manual parse, but had issues with the optional microsecond segment which gave me the following
CREATE OR REPLACE FUNCTION varchar_to_timestamptz (val varchar) RETURNS timestamptz AS $ $ SELECT CASE WHEN $ 1 LIKE '%.%' THEN to_timestamp($ 1, 'YYYY-MM-DD"T"HH24:MI:SS.USZ')::timestamp without time zone at time zone 'Etc/UTC' ELSE to_timestamp($ 1, 'YYYY-MM-DD"T"HH24:MI:SSZ')::timestamp without time zone at time zone 'Etc/UTC' END $ $ LANGUAGE SQL;
Which worked, but didn’t feel correct.
Is there a better way to approach this implicit cast?