CODEWITHSUNDEEP
amazon-redshifttimestamp-with-timezoneamazon-redshift-spectrum
Isha Garg
Isha Garg

Reputation: 351

How to convert a varchar data type field to a timestamp with time zone type field in redshift?

I have a table where the timestamp is stored as a varchar. I need to convert it to timestamp with timezone but every time I get "Invalid Operation" error.

The format of the field is: 

2017-10-30 10:12:34:154 +1100

I tried the following:

'2017-10-30 10:12:34:154 +1100'::timestamptz
'2017-10-30 10:12:34:154 +1100'::timestamp
to_timestamp('2017-10-30 10:12:34:154 +1100')
to_date('2017-10-30 10:12:34:154 +1100')
to_timestamp(to_char('2017-10-30 10:12:34:154 +1100'))

All gave an error like this:

[Amazon](500310) Invalid operation: function to_timestamp(character varying) does not exist;

Can somebody please help?

Upvotes: 0

Views: 689

Answers (2)

Isha Garg
Isha Garg

Reputation: 351

For the benefit of all, here's how I solved my question. Basically, the problem was that the acceptable syntax for timestamp with timezone in redshift was '2017-10-30 10:12:34.154 +1100' where the milliseconds was after the '.' and not after ':'. Although, I am not sure of this. But this seemed to be the only problem from the redshift documentation and playing around a bit.

The substr_replace didn't work in my case since the length of the date and time keeps varying like these two -

'2017-10-30 10:12:34:154 +1100' , '2017-10-3 5:12:34:154 +1100'

So, I split all three fields and added them back in proper syntax after removing milliseconds since I didn't need that.

(split_part(m.properties_date_time_tz,' ',1)||' '||substring(split_part(m.properties_date_time_tz,' ',2),0,length(split_part(m.properties_date_time_tz,' ',2))-3)||' '||split_part(m.properties_date_time_tz,' ',3))::timestamptz

where properties_date_time_tz had a value like '2017-10-30 10:12:34:154 +1100'

Upvotes: 0

Tom
Tom

Reputation: 172

To Redshift's to_timezone does not support offsets so you need to mangle it as follows:

select DATEADD('ms',
           (SUBSTRING('2017-10-30 10:12:34:154 +1100',21,3 ))::int,
           (SUBSTRING('2017-10-30 10:12:34:154 +1100', 0,20) + SUBSTRING('2017-10-30 10:12:34:154 +1100', 24,6))::timestamp)

Upvotes: 1

Related Questions