Subtracting DATEs in Oracle – How is the Result Stored Internally?

Recently, I posted a question on Stack Overflow regarding the internal representation of the result of DATE subtractions in Oracle. However, no one seemed to be sure of how this data was stored, so I took things into my own hands and did a bit of experimentation to try and find a definitive answer.

Background:

According to the Oracle 11.2 SQL Reference manual, when you subtract 2 DATE datatypes, the result will be a NUMBER datatype.

On cursory testing, this appeared to be true:

Listing 1

SQL> CREATE TABLE test (start_date DATE);
SQL> INSERT INTO test (start_date) VALUES (date'2004-08-08');
SQL> SELECT (SYSDATE - start_date) from test;

(SYSDATE-START_DATE)
--------------------
           2751.5505

However, I was reminded of a piece of code I saw that went something along the lines of:

Listing 2

SQL> SELECT (SYSDATE - start_date) DAY(5) TO SECOND from test;

(SYSDATE-START_DATE)DAY(5)TOSECOND
----------------------------------
+02751 13:13:41.000000

The result is an INTERVAL datatype. In other words, Oracle seems to be able to convert the NUMBER resulting from the DATE subtraction into an INTERVAL type.

With this thought in mind, I figured I could try putting in a NUMBER datatype directly in the brackets (instead of doing ‘SYSDATE – start_date’ which results in a NUMBER datatype anyways):

Listing 3

SQL> SELECT (2751.5505) DAY(5) TO SECOND from test;

But this resulted in the error:

SELECT (2751.5505) DAY(5) TO SECOND from test;
                   *
ERROR at line 1:
ORA-30083: syntax error was found in interval value expression

At this point, I was a bit confused about what was going on. It seems like subtracting dates should lead to a NUMBER (as demonstrated by listing #1), which cannot be automatically cast to INTERVAL type (as demonstrated in listing #3). But Oracle seems to be able to do this conversion somehow if you use the DATE subtraction expression instead of putting in a raw NUMBER (SELECT listing #2).

Hence, I decided to dig a little deeper into this matter.

Solution:

When you subtract 2 dates, the value is actually stored as 2 separate two’s complement signed binary numbers, with the first 4 bytes used to represent the number of days and the last 4 bytes used to represent the number of seconds.

An example of a DATE subtraction resulting in a positive integer difference:

SQL> select date '2009-08-07' - date '2008-08-08' from dual;

DATE'2009-08-07'-DATE'2008-08-08'
---------------------------------
                              364

SQL> select dump(date '2009-08-07' - date '2008-08-08') from dual;

DUMP(DATE'2009-08-07'-DATE'2008
-------------------------------
Typ=14 Len=8: 108,1,0,0,0,0,0,0

Note that the internal datatype number of a DATE subtraction is 14, which is a non-documented internal datatype (NUMBER is internal datatype number 2). Recall that the result is represented as a 2 seperate two’s complement signed 4 byte binary numbers. Since there are no fractions in this case (364 days and 0 hours exactly), the last 4 bytes are all 0s and can be ignored. For the first 4 bytes, because my CPU uses little-endian architecture, the bytes are reversed and should be read as 1,108 or 0x16c or 00000001 01101100 base 2, which converts to 364 base 10.

An example of a DATE subtraction resulting in a negative integer difference:

SQL> select date '1000-08-07' - date '2008-08-08' from dual;

DATE'1000-08-07'-DATE'2008-08-08'
---------------------------------
                          -368160

SQL> select dump(date '1000-08-07' - date '2008-08-08') from dual;

DUMP(DATE'1000-08-07'-DATE'2008-08-0
------------------------------------
Typ=14 Len=8: 224,97,250,255,0,0,0,0

Again, since I am using a little-endian machine, the bytes are reversed and should be read as 255,250,97,224 which corresponds to 11111111 11111010 01100001 11011111. Now since this is in two’s complement signed binary numeral encoding, we know that the number is negative because the leftmost binary digit is a 1. To convert this into a decimal number we would have to reverse the 2’s complement (subtract 1 then do the one’s complement) resulting in: 00000000 00000101 10011110 00100000 base 2 which equals -368160 base 10 as suspected.

An example of a DATE subtraction resulting in a decimal difference:

SQL> select to_date('08/AUG/2004 14:00:00', 'DD/MON/YYYY HH24:MI:SS' - to_date('08/AUG/2004 8:00:00', 'DD/MON/YYYY HH24:MI:SS') from dual;

TO_DATE('08/AUG/200414:00:00','DD/MON/YYYYHH24:MI:SS')-TO_DATE('08/AUG/20048:00:
--------------------------------------------------------------------------------
                                                                             .25

The difference between those 2 dates is 0.25 days or 6 hours.

SQL> select dump(to_date('08/AUG/2004 14:00:00', 'DD/MON/YYYY HH24:MI:SS') - to_date('08/AUG/2004 8:00:00', 'DD/MON/YYYY HH24:MI:SS')) from dual;

DUMP(TO_DATE('08/AUG/200414:00:
-------------------------------
Typ=14 Len=8: 0,0,0,0,96,84,0,0

Now this time, since the difference is 0 days and 6 hours, it is expected that the first 4 bytes are 0. For the last 4 bytes, we again read them in reverse (little-endian) and get 84,96 = 01010100 01100000 base 2 = 21600 base 10. Converting 21600 seconds to hours gives you 6 hours which is the difference which we expected.

Hopefully, this little exercise helps anyone who has ever wondered how a DATE subtraction is actually stored.

Follow

Get every new post delivered to your Inbox.