My table has a column _range (type: interval) that I want to use to divide another column _count (type: double), but I got this error:

Cannot apply operator: double / interval day to second

So I tried converting _range to double so I could divide double by double like this:

select _count / cast(_range as double)

but I get this error:

Cannot cast interval day to second to double

Any suggestions on how to resolve this?

CodePudding user response：

Use to_milliseconds (and divide it by corresponding number if you need seconds, minutes, hours, days):

to_milliseconds(interval) → bigint
Returns the day-to-second interval as milliseconds.

select typeof(INTERVAL '2' DAY), to_milliseconds(INTERVAL '2' DAY)

Output: