The CEIL function, dates and floating point numbers

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 all software products must come to grips with, namely, we don’t have infinite precision on a chip.

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.