Oh yes indeed, I have dug deep into the well of cheesy blog post titles for this one . But hey, I have two teenage children so I figure that I have reached the age where you’ll need to permit me my share of terrible Dad-joke style puns.

Consider the two dates below (both of which include a time component):

```
04-SEP-2020 00:00:00
05-SEP-2020 00:08:02
```

Let us do some quick arithmetic in our heads. How many minutes are there between those two dates? To avoid any reader headaches I’ll throw in the fact that there are 1440 minutes in a day. So there is one day between them (1440) plus an additional 8 minutes past midnight bringing us to 1448, plus a couple of seconds left over.

If we have been tasked to derive the minutes between those two date/times, we probably need to take into account the seconds as well, and for the sake of discussion, we’ll assume that any portion of a minute should be rounded up to the next minute. That all flows nicely into the following expression to derive the minutes between the two dates:

```
```**ceil(** ( date2 - date1 ) * 1440 **)**

I’ll apply that expression to the two values above, and all works as expected

```
SQL> with t as
2 ( select
3 to_date('04-SEP-2020 00:00:00','DD-Mon-YYYY HH24:MI:SS') d1,
4 to_date('05-SEP-2020 00:08:02','DD-Mon-YYYY HH24:MI:SS') d2
5 from dual
6 )
7 select ceil((d2-d1)*1440) x
8 from t;
```** X
----------
1449**

Bear with me, we’re going to move past the realm of the bleedin’ obvious shortly.

Now let me adjust the dates a little, to the values below:

```
04-SEP-2020 00:00:00
05-SEP-2020 00:08:00
```

All I have done is remove the seconds, so this seems an even easier proposition because we have an exact number of minutes, namely 1448. I’ll re-run my SQL and lets look at the result:

```
SQL> with t as
2 ( select
3 to_date('04-SEP-2020 00:00:00','DD-Mon-YYYY HH24:MI:SS') d1,
4 to_date('05-SEP-2020 00:08:00','DD-Mon-YYYY HH24:MI:SS') d2
5 from dual
6 )
7 select ceil((d2-d1)*1440) x
8 from t;
```** X
----------
1449**

Hmmmm…not go great. This is the danger that is inherent in floating point arithmetic. Being able to use numeric expressions and operators with dates is a very cool feature of the Oracle Database, but whenever numbers get involved, you should always be on the look out for potential risks that ** all** numeric operations in

So what is happening here? To find out, we need to remove the CEIL function and pad out the precision of our result a little

```
SQL> col x format 99999.999999999999999999999999999999999999999
SQL> with t as
2 ( select
3 to_date('04-SEP-2020 00:00:00','DD-Mon-YYYY HH24:MI:SS') d1,
4 to_date('05-SEP-2020 00:08:00','DD-Mon-YYYY HH24:MI:SS') d2
5 from dual
6 )
7 select (d2-d1)*1440 x
8 from t;
X
----------------------------------------------
```** 1448.000000000000000000000000000000000006000**

As you can see, the operation was out by a mere 6 undecillionths of a day, and no, I did not make the term undecillion up .

But CEIL does not care about magnitudes; it sees that the result is ever so slightly larger than 1448 and thus takes it up to the ceiling integer of 1449. You might be thinking *“We’ll just swap CEIL for ROUND”* but that does ** not** meet the requirement of partial minutes always being taken up to the next whole minute.

CEIL is indeed the correct operation here, but we need to apply it only once we have explicitly taken care of any floating point anomalies. There are only 86400 seconds in a day, so the absolute most precision we will need for the difference between 2 date/times is 5 decimal places. Thus the correct expression should be:

```
SQL> with t as
2 ( select
3 to_date('04-SEP-2020 00:00:00','DD-Mon-YYYY HH24:MI:SS') d1,
4 to_date('05-SEP-2020 00:08:00','DD-Mon-YYYY HH24:MI:SS') d2
5 from dual
6 )
7 select ceil(
```**round(**(d2-d1)*1440**,5)**) x
8 from t;
X
----------
1448
SQL> with t as
2 ( select
3 to_date('04-SEP-2020 00:00:00','DD-Mon-YYYY HH24:MI:SS') d1,
4 to_date('05-SEP-2020 00:08:02','DD-Mon-YYYY HH24:MI:SS') d2
5 from dual
6 )
7 select ceil(**round(**(d2-d1)*1440**,5)**) x
8 from t;
X
----------
1449

*TL;DR: If you’re using numeric operations in Oracle, and not using the NUMBER data type directly, then it is your responsibility to deal with the nuances of floating point arithmetic that all computers perform. *

- September 2020 (26)
- August 2020 (54)
- July 2020 (38)
- June 2020 (49)
- May 2020 (50)
- April 2020 (64)
- March 2020 (62)
- February 2020 (59)
- January 2020 (90)
- December 2019 (48)
- November 2019 (47)
- October 2019 (64)
- September 2019 (40)
- August 2019 (42)
- July 2019 (45)
- June 2019 (37)
- May 2019 (43)
- April 2019 (49)
- March 2019 (61)
- February 2019 (25)
- January 2019 (35)
- December 2018 (39)
- November 2018 (55)
- October 2018 (75)
- September 2018 (38)
- August 2018 (89)
- July 2018 (58)
- June 2018 (61)
- May 2018 (66)
- April 2018 (40)

## Recent comments

2 years 34 weeks ago

2 years 46 weeks ago

2 years 50 weeks ago

2 years 51 weeks ago

3 years 4 weeks ago

3 years 25 weeks ago

3 years 45 weeks ago

4 years 23 weeks ago

5 years 7 weeks ago

5 years 7 weeks ago