Search code examples
sqlintervalspresto

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?

Solution

  • 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