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

Question

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?

Tom · Answer 1 · 2017-11-08T08:34:35.260

1

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)

edited Nov 08 '17 at 08:34

answered Nov 07 '17 at 11:28

Tom

172
6

Thanks for replying. I am getting an error like this: `[Amazon](500310) Invalid operation: "OF" not supported;` – Isha Garg Nov 08 '17 at 06:47
apologies. please see above – Tom Nov 08 '17 at 08:34
Thanks alot @Tom. Your solution helped me in reaching to my final solution. – Isha Garg Dec 04 '17 at 11:15

score 0 · Accepted Answer · answered Dec 04 '17 at 11:14

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'

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

2 Answers2