How to convert text to timestamp with adjusted format?-CodePudding

I have a table that one of its column contains timestamp.

This column defined as text field.

The timestamp format is as follow:

"2021-01-05T:12:29:30"
"2021-05-15T:15:23:00"

How can I convert this text field to timestamp using TO_TIMESTAMP function ?

CodePudding user response：

You can use TO_TIMESTAMP with a format

to_timestamp(you_text_column,'YYYY-MM-DDT:hh24:mi:ss') as your_timestamp

But somehow that odd format you have can also be type casted to a timestamp

select '2021-01-05T:12:29:30'::timestamp

I'm calling it odd because it's not even in an ISO 8601 format.
Since it has that extra :