CODEWITHSUNDEEP
sqlintervalspresto
ebergeson
ebergeson

Reputation: 65

SQL Error: Cannot cast interval day to second to double (Presto)

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?

Upvotes: 3

Views: 3739

Answers (1)

Guru Stron
Guru Stron

Reputation: 142213

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:

_col0 _col1
interval day to second 172800000

Upvotes: 3

Related Questions