DBA Hub

📋Steps in this guide1/4

CEIL and FLOOR Support DATE, TIMESTAMP, and INTERVAL Data Types in Oracle Database 23ai/26ai

In Oracle database 23ai/26ai the CEIL and FLOOR functions support the DATE, TIMESTAMP and INTERVAL data types.

oracle 23configurationintermediate
by OracleDba
14 views
1

Setup

The examples in this article use the following table. We display the data in the table. This represents our starting point.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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;

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>
2

CEIL

The function rounds up , and values. This is similar to using the function, but the function will always round up. The format parameter defaults to 'DD', so without the format parameter we round up to the following day. If we compare this to the function, we can see that depending on the value, it may be rounded up or down. The format parameter determines the level of rounding. In the following example we use 'HH24' to round up to the next hour. Once gain, if we compare this to the function, we can see that depending on the value, it may be rounded up or down.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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>

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>

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>

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>
3

FLOOR

The function rounds down , and values. This is similar to using the 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. Here we see the equivalent using the function. The format parameter determines the level of rounding. In the following example we use 'HH24' to round down to the previous hour. Here we see the equivalent using the function.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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>

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>

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>

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>
4

Format

The available formats for the , , , and 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...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!