CODEWITHSUNDEEP
sqlamazon-redshift
Matthew Justin
Matthew Justin

Reputation: 83

Redshift ERROR: Column (name) has unsupported type interval

In Redshift I'm trying to create a table wherein I subtract a datetime from another datetime, where the original data is a string, and arrive at a difference to the nearest millisecond.

(SPLIT_PART((MAX(event_start_datetime_ms) OVER (partition by visit_id)), 'T', 2) :: time - SPLIT_PART(event_start_datetime_ms,'T',2) :: time) as time_diff

I can run a select statement fine, but when trying to create a table I'm getting the following:

[0A000] ERROR: Column "time_diff" has unsupported type "interval".

Apparently Redshift tables can't handle the "interval" data type. I tried casting it as a string but that mangles the results. Casting it as time gives this error:

ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.

Any suggestions?

Upvotes: 1

Views: 702

Answers (1)

SelVazi
SelVazi

Reputation: 16083

It appears that the Window function within a function is not supported, Try this instead :

select SPLIT_PART(max_event_start_datetime_ms, 'T', 2) :: time 
       - SPLIT_PART(event_start_datetime_ms, 'T', 2) :: time  as time_diff
from (
  select *, MAX(event_start_datetime_ms) OVER (partition by visit_id) max_event_start_datetime_ms
  from mytable
) as s

Upvotes: 0

Related Questions