8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
CEIL and FLOOR Support DATE, TIMESTAMP, and INTERVAL Data Types in Oracle Database 23c
In Oracle database 23c the CEIL and FLOOR functions support the DATE, TIMESTAMP and INTERVAL data types. In previous releases the CEIL and FLOOR functions would only accept numbers, allowing us to round up or down to the nearest integer value respectively.
Setup
The examples in this article use the following table.
drop table if exists t1 purge;
create table t1 (
id number,
number_col number,
date_col date,
timestamp_col timestamp,
interval_col interval day to second
);
insert into t1 (id, number_col, date_col, timestamp_col, interval_col) values
(1, 1.3, to_date('2023-05-01 08:15','yyyy-mm-dd hh24:mi'), timestamp '2023-05-01 08:15:00.0', to_dsinterval('0 08:15:00')),
(2, 1.6, to_date('2023-05-01 13:45','yyyy-mm-dd hh24:mi'), timestamp '2023-05-01 13:45:00.0', to_dsinterval('0 13:45:00'));
commit;
We display the data in the table. This represents our starting point.
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';
set linesize 100
column date_col format a20
column timestamp_col format a20
column interval_col format a30
select * from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 1.3 2023-05-01 08:15:00 2023-05-01 08:15:00 +00 08:15:00.000000
2 1.6 2023-05-01 13:45:00 2023-05-01 13:45:00 +00 13:45:00.000000
SQL>
CEIL
The CEIL function rounds up DATE, TIMESTAMP and INTERVAL values. This is similar to using the ROUND function, but the CEIL function will always round up. The format parameter defaults to 'DD', so without the format parameter we round up to the following day.
select id,
ceil(number_col) as number_col,
ceil(date_col) as date_col,
ceil(timestamp_col) as timestamp_col,
ceil(interval_col) as interval_col
from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 2 2023-05-02 00:00:00 2023-05-02 00:00:00 +000000001 00:00:00.000000000
2 2 2023-05-02 00:00:00 2023-05-02 00:00:00 +000000001 00:00:00.000000000
SQL>
If we compare this to the ROUND function, we can see that depending on the value, it may be rounded up or down.
select id,
round(number_col) as number_col,
round(date_col) as date_col,
round(timestamp_col) as timestamp_col,
round(interval_col) as interval_col
from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 1 2023-05-01 00:00:00 2023-05-01 00:00:00 +000000000 00:00:00.000000000
2 2 2023-05-02 00:00:00 2023-05-02 00:00:00 +000000001 00:00:00.000000000
SQL>
The format parameter determines the level of rounding. In the following example we use 'HH24' to round up to the next hour.
select id,
ceil(number_col) as number_col,
ceil(date_col, 'hh24') as date_col,
ceil(timestamp_col, 'hh24') as timestamp_col,
ceil(interval_col, 'hh24') as interval_col
from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 2 2023-05-01 09:00:00 2023-05-01 09:00:00 +000000000 09:00:00.000000000
2 2 2023-05-01 14:00:00 2023-05-01 14:00:00 +000000000 14:00:00.000000000
SQL>
Once gain, if we compare this to the ROUND function, we can see that depending on the value, it may be rounded up or down.
select id,
round(number_col) as number_col,
round(date_col, 'hh24') as date_col,
round(timestamp_col, 'hh24') as timestamp_col,
round(interval_col, 'hh24') as interval_col
from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 1 2023-05-01 08:00:00 2023-05-01 08:00:00 +000000000 08:00:00.000000000
2 2 2023-05-01 14:00:00 2023-05-01 14:00:00 +000000000 14:00:00.000000000
SQL>
FLOOR
The FLOOR function rounds down DATE, TIMESTAMP and INTERVAL values. This is similar to using the TRUNC function with a format parameter. The format parameter defaults to 'DD', so without the format parameter we round down to the start of the day.
select id,
floor(number_col) as number_col,
floor(date_col) as date_col,
floor(timestamp_col) as timestamp_col,
floor(interval_col) as interval_col
from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 1 2023-05-01 00:00:00 2023-05-01 00:00:00 +000000000 00:00:00.000000000
2 1 2023-05-01 00:00:00 2023-05-01 00:00:00 +000000000 00:00:00.000000000
SQL>
Here we see the equivalent using the TRUNC function.
select id,
trunc(number_col) as number_col,
trunc(date_col) as date_col,
trunc(timestamp_col) as timestamp_col,
trunc(interval_col) as interval_col
from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 1 2023-05-01 00:00:00 2023-05-01 00:00:00 +000000000 00:00:00.000000000
2 1 2023-05-01 00:00:00 2023-05-01 00:00:00 +000000000 00:00:00.000000000
SQL>
The format parameter determines the level of rounding. In the following example we use 'HH24' to round down to the previous hour.
select id,
floor(number_col) as number_col,
floor(date_col, 'hh24') as date_col,
floor(timestamp_col, 'hh24') as timestamp_col,
floor(interval_col, 'hh24') as interval_col
from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 1 2023-05-01 08:00:00 2023-05-01 08:00:00 +000000000 08:00:00.000000000
2 1 2023-05-01 13:00:00 2023-05-01 13:00:00 +000000000 13:00:00.000000000
SQL>
Here we see the equivalent using the TRUNC function.
select id,
trunc(number_col) as number_col,
trunc(date_col, 'hh24') as date_col,
trunc(timestamp_col, 'hh24') as timestamp_col,
trunc(interval_col, 'hh24') as interval_col
from t1;
ID NUMBER_COL DATE_COL TIMESTAMP_COL INTERVAL_COL
---------- ---------- -------------------- -------------------- ------------------------------
1 1 2023-05-01 08:00:00 2023-05-01 08:00:00 +000000000 08:00:00.000000000
2 1 2023-05-01 13:00:00 2023-05-01 13:00:00 +000000000 13:00:00.000000000
SQL>
Format
The available formats for the CEIL, FLOOR, ROUND, and TRUNC date functions are listed CEIL, FLOOR, ROUND, and TRUNC Date Functions.
For more information see:
- CEIL(datetime)
- CEIL(interval)
- FLOOR(datetime)
- FLOOR(interval)
- CEIL, FLOOR, ROUND, and TRUNC Date Functions
Hope this helps. Regards Tim...