description: Currently RANGE clause supports only numeric values for PRECEDING and FOLLOWING borders. However, if an expression in the ORDER BY clause is a type of TIMESTAMP is difficult to set any interval except for days.

Example 1. Interval - 3 days

select
bydate,
total,
sum(total) over w as s_3days
from sales
window w as (order by bydate range between 3 preceding and current row)

Example 2. Interval - 3 hours

select
bydate,
total,
sum(total) over w as s_3hours
from sales
window w as (order by bydate range between 3 * 1.000 / 24 preceding and current row)

The last example is not very beautiful. Oracle Database uses the following syntax for time slots.

INTERVAL <expr> {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}

In this case, the second example would look like this

select
bydate,
total,
sum(total) over w as s_3hours
from sales
window w as (order by bydate range between interval 3 hour preceding and current row)

=>

Currently RANGE clause supports only numeric values for PRECEDING and FOLLOWING borders. However, if an expression in the ORDER BY clause is a type of TIMESTAMP is difficult to set any interval except for days.

Example 1. Interval - 3 days

select
  bydate,
  total,
  sum\(total\) over w as s\_3days
from sales
window w as \(order by bydate range between 3  preceding and current row\)

Example 2. Interval - 3 hours

select
  bydate,
  total,
  sum\(total\) over w as s\_3hours
from sales
window w as \(order by bydate range between 3 \* 1\.000 / 24 preceding and current row\)

The last example is not very beautiful. Oracle Database uses the following syntax for time slots.

INTERVAL <expr> {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}

In this case, the second example would look like this

select
  bydate,
  total,
  sum\(total\) over w as s\_3hours
from sales
window w as \(order by bydate range between interval 3 hour preceding and current row\)