More Powerful FLOOR and CEIL Functions in Oracle Database 23c

The FLOOR and CEIL functions have been enhanced in Oracle Database 23c (Developer’s Edition) to allow dates, timestamps, and intervals in addition to just numeric values. Of course, there’s going to be an Exadata consideration too!

New Arguments for FLOOR and CEIL

When you use FLOOR or CEIL in 23c, you can now have DATE, TIMESTAMP, or INTERVALs as arguments. Much like the ROUND or TRUNC functions in 19c, you’ll need a second argument to specify precision. ROUND and TRUNC were already able to have dates as arguments and accept any valid date format model but can also have INTERVALs as the first argument.

By default, the FLOOR and CEIL functions round date or timestamp values to the nearest day (‘DD’):

select ceil(to_date('2023-07-04 05:10:23','YYYY-MM-DD HH24:MI:SS'));

CEIL(TO_DATE('2023-
-------------------
2023-07-05 00:00:00

If I wanted to round to the next highest month, for example, I’d have to specify ‘MM’ as the second argument:

select ceil(to_date('2023-07-04 05:10:23','YYYY-MM-DD HH24:MI:SS'),'MM');

CEIL(TO_DATE('2023-
-------------------
2023-08-01 00:00:00

But What About Exadata?

Any built-in single row function in Oracle becomes even more powerful when you can offload that function to the storage layer and let the storage servers do much of the heavy lifting by filtering the result set before sending it back to the compute layer. Without having a 23c environment on Exadata yet, the only way I can predict how effective that offloading will be is to check the view V$SQLFN_METADATA:

select func_id, name, minargs, maxargs, datatype, version, offloadable
from v$sqlfn_metadata
where name in ('FLOOR','CEIL')
order by name, func_id;

In lines 4-5 are the new versions of CEIL and lines 9-10 for FLOOR, both with 2 arguments. Presumably “ARG 1” will eventually say “INTERVAL” and the VERSION will not be “INVALID”. I suspect that will be corrected by the GA release of 23c.

More importantly, the OFFLOADABLE column is “YES” for all versions of those functions, and I’m looking forward to verifying that on Exadata in the near future!