Convert text column to timestamp in Amazon redshift

Question

I have a text field "completed_on" with text values "Thu Jan 27 2022 11:55:12 GMT+0530 (India Standard Time)". I need to convert this into timestamp.

I tried , cast(completed_on as timestamp) which should give me the timestamp but I am getting the following error in REDSHIFT

ERROR: Char/varchar value length exceeds limit for date/timestamp conversions

Answer 1

Since timestamps can be in many different formats, you need to tell Amazon Redshift how to interpret the string.

From TO_TIMESTAMP function - Amazon Redshift:

TO_TIMESTAMP converts a TIMESTAMP string to TIMESTAMPTZ.

select sysdate, to_timestamp(sysdate, 'YYYY-MM-DD HH24:MI:SS') as seconds;


timestamp                  | seconds
-------------------------- | ----------------------
2021-04-05 19:27:53.281812 | 2021-04-05 19:27:53+00

For formatting, see: Datetime format strings - Amazon Redshift.