Subtracting Dates in Oracle – Number or Interval Datatype?

by

Ok, I don’t normally answer my own questions but after a bit of tinkering, I have figured out definitively how Oracle stores the result of a DATE subtraction.

When you subtract 2 dates, the value is not a NUMBER datatype (as the Oracle 11.2 SQL Reference manual would have you believe). The internal datatype number of a DATE subtraction is 14, which is a non-documented internal datatype (NUMBER is internal datatype number 2). However, it is actually stored as 2 separate two’s complement signed 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:

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

Results in:

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

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

Recall that the result is represented as a 2 seperate two’s complement signed 4 byte numbers. Since there are no decimals 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 has a little-endian architecture, the bytes are reversed and should be read as 1,108 or 0x16c, which is decimal 364.

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

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

Results in:

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

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 which equals -368160 as suspected.

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

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.

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 can reverse them (because CPU is little-endian) and get 84,96 = 01010100 01100000 base 2 = 21600 in decimal. Converting 21600 seconds to hours gives you 6 hours which is the difference which we expected.

Hope this helps anyone who was wondering how a DATE subtraction is actually stored.

More Related Contents:

  1. REGEX to select nth value from a list, allowing for nulls
  2. PLS-00428: an INTO clause is expected in this SELECT statement
  3. Sleep function in ORACLE
  4. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  5. How to call Oracle MD5 hash function?
  6. Using bind variables with dynamic SELECT INTO clause in PL/SQL
  7. Current date in Oracle Db
  8. I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]
  9. Is there a combination of “LIKE” and “IN” in SQL?
  10. How do I do top 1 in Oracle? [duplicate]
  11. SQL to generate a list of numbers from 1 to 100
  12. How to use BOOLEAN type in SELECT statement
  13. SQL recursive query on self referencing table (Oracle)
  14. create table with sequence.nextval in oracle [duplicate]
  15. IF EXISTS condition not working with PLSQL
  16. Using pivot on multiple columns of an Oracle row
  17. WHERE IN condition not accepting String value
  18. Pivoting of data using two columns
  19. Check if current date is between two dates Oracle SQL
  20. Solution to “cannot perform a DML operation inside a query”?
  21. Array in IN() clause oracle PLSQL
  22. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  23. How to query a CLOB column in Oracle
  24. Oracle (Old?) Joins – A tool/script for conversion?
  25. Creating or simulating two dimensional arrays in PL/SQL
  26. Why does the wm_concat not work here?
  27. Oracle string aggregation
  28. Curious issue with Oracle UNION and ORDER BY
  29. ORACLE After update trigger: solving ORA-04091 mutating table error
  30. Insert into from CTE

Leave a Comment