CODEWITHSUNDEEP
amazon-web-servicestimestampamazon-redshiftto-date
veerendra bellapukonda
veerendra bellapukonda

Reputation: 51

Convert text column to timestamp in Amazon redshift

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

Upvotes: 0

Views: 453

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269191

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.

Upvotes: 1

Related Questions