CODEWITHSUNDEEP
sqlcsvamazon-redshift
Estrobelai
Estrobelai

Reputation: 309

Unusual date format (string) to datetime in AWS Redshift SQL

I am currently pulling data (.CSV) off this API that I built in Java.

One of the columns from my CSV table - "created_at" - is coming under the string format as the below:

enter image description here

Is there anyway I can transform this in Redshift SQL to datetime under the following format:

enter image description here

Any help is appreciated.

Upvotes: 0

Views: 1296

Answers (2)

Rajat
Rajat

Reputation: 5803

See if this helps (updated)

select cast('Thu Sep 12 17:58:46 BST 2019' as timestamp);

Or simply,

select 'Thu Sep 12 17:58:46 BST 2019':: timestamp

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

If you can live without the timezone, you can try:

select to_timestamp(left(str, 20) || right(str, 4), 'Dy Mon DD HH24:MI:SS YYYY')
from (select 'Thu Sep 12 17:58:46 BST 2019' as str) v;

Upvotes: 0

Related Questions