How to convert YYYY-MM-DDTHH:mm:ss.SSSZ timestamp to YYYY-MM-DD HH:mm:ss in Postgres-CodePudding

Problem Summary

I would like to convert and validate the timestamp which is in YYYY-MM-DDTHH:mm:ss.SSSZ format (ex: 2022-06-15T08:27:00.599Z) in postgres.

I tried

select * from my_table WHERE my_date >= date_trunc('second', '2022-06-15T08:27:00.599Z'::timestamp)

select to_char(to_timestamp('2022-06-15T08:27:00.599Z','YYYY-MM-DD\"T\"HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')

These queries are executing fine in db_fiddle but giving an errors while running in python and postgres SQL.

Facing errors

RROR: TEIID31100 Parsing error: Encountered "'2022-06-15T08:27:00.599Z','YYYY-MM-DD"T"HH24:MI:SS), '[]YYYY[]-MM" at line 1, column 672.Was expecting: "and" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "order" | "similar" ...org.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "'2022-06-15T08:27:00.599Z','YYYY-MM-DD"T"HH24:MI:SS), '[]YYYY[]-MM" at line 1, column 672.Was expecting: "and" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "order" | "similar" ...;Error while executing the query nil

My need

Input timestamp : 2022-06-15T08:27:00.599Z
Expected timestamp : 2022-06-15 08:27:00

Appreciate your support.

CodePudding user response：

Forgive me if I am oversimplifying your question, but wouldn't a simple cast and date_trunc do the trick?

SELECT date_trunc('second','2022-06-15T08:27:00.599Z'::timestamp);

     date_trunc      
---------------------
 2022-06-15 08:27:00

An alternative is to round the seconds with ::timestamp(0) - see this other answer. But it would return a different result:

SELECT '2022-06-15T08:27:00.599Z'::timestamp(0);

      timestamp      
---------------------
 2022-06-15 08:27:01

CodePudding user response：

There is no specific function available in teiid functions to convert my desired TS format. I used substr to capture the required TS in my case. It worked.

Reference link teiid