Subtracting Dates in Oracle - Number or Interval Datatype

Subtracting Dates in Oracle - Number or Interval Datatype?

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.

Oracle : how to subtract two dates and get seconds of the result

Your data type is most probably TIMESTAMP that would explain the rounding problem.

You may workaround it by first casting to DATE (to get rid of the milliseconds) and that casting it back to TIMESTAMP (to be able to perform your regexp_substr)

This sample data replays your problem

select opa.*, 
NVL(REGEXP_SUBSTR (CAST(opa.end_time AS TIMESTAMP) - CAST(opa.start_time AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration,
NVL(REGEXP_SUBSTR (CAST(CAST(opa.end_time AS DATE)AS TIMESTAMP) - CAST(CAST(opa.start_time AS DATE)AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration2
from tab opa;

START_TIME                           END_TIME                             DURATION                    DURATION2                   
------------------------------------ ------------------------------------ --------------------------- ---------------------------
04.05.2021 09:13:07,555000000 +02:00 04.05.2021 09:13:18,111000000 +02:00 00:00:10                    00:00:11 

How to get Time Differences of Two Dates like x days y hours z seconds t minutes

According to the datetime arithmetic matrix difference of two date values is a number, which is the number of days. As a pure number it has no time components. So it should be converted to the interval day to second with numtodsinterval function:

with a as (
  select
    sysdate as dt1,
    trunc(sysdate) as dt2
  from dual
)
select
  numtodsinterval(dt1 - dt2, 'DAY') as interval_
from a

| INTERVAL_                     |
| :---------------------------- |
| +000000000 22:38:59.000000000 |

db<>fiddle here

But if you have timestamp, then according to the same matrix the difference will be interval by default:

with a as (
  select
    sysdate as dt1,
    trunc(sysdate) as dt2
  from dual
)
select
  numtodsinterval(dt1 - dt2, 'DAY') as interval_
from a

| INTERVAL_                     |
| :---------------------------- |
| +000000000 22:38:59.000000000 |

db<>fiddle here

Note that interval has no format in to_char function, so to retrieve hours, minutes, seconds you will need to use extract function

How can I get the number of days between 2 dates in Oracle 11g?

I figured it out myself. I need

select extract(day from sysdate - to_date('2009-10-01', 'yyyy-mm-dd')) from dual

How to calculate exact hours between two datetime fields?

The 'format' comment on your first query suggests your columns are timestamps, despite the dummy column names, as the result of subtracting two timestamps is an interval. Your second query is implicitly converting both timestamps to dates before subtracting them to get an answer as a number of days - which would be fractional if you weren't truncating them and thus losing the time portion.

You can extract the number of hours from the interval difference, and also 24 * the number of days if you expect it to exceed a day:

extract(day from (date1 - date2)) * 24 + extract(hour from (date1 - date2))

If you want to include fractional hours then you can extract and manipulate the minutes and seconds too.

You can also explicitly convert to dates, and truncate or floor after manipulation:

floor((cast(date1 as date) - cast(date2 as date)) * 24)

db<>fiddle demo

Timestamp subtraction

When you subtract one value that is of timestamp data type from another one, which is also of timestamp data type, result of that subtraction will be of interval data type, not timestamp, so when you are trying to select/assign a value of interval data type into/to a variable that is of timestamp data type, you will inevitably receive the PL/SQL: ORA-00932:/PLS-00382 error message. A simple solution is to assign the result of timestamp subtraction to a variable of interval data type. To that end you:

1. Re-declare your vl_diff variable as a variable of interval data type:

   vl_diff interval day to second;
   

2. use extract() function to extract minutes from the value assigned to vl_diff variable:

   extract(minute from vl_diff)
   

   Your second query might look like this:

   select case when count(1) > 0 then 1 else 2 end
     into vl_val
     from sessions
    where login_details = p_login 
      and extract(minute from vl_diff) > 30
   

Subtract interval from Date in Oracle

You can use SYSDATE (oracle specific) or CURRENT_DATE (ANSI) to get the current date/time.

In SQL Developer you can set the date format by going to "Tools" > "Preferences" and selecting the "Database" > "NLS" on the left hand side and then editing the Date Format.

If you want to use a different date format most of the time but in this singular instance want the date in ISO 8601 format (YYYY-MM-DD) then you can use TO_CHAR( date, 'YYYY-MM-DD') (but it will then return as a string rather than a date).

To add (subtract) days from a date then you can do one of several options (note: SQLFIDDLE has different NLS date formatting perameters on its output that you've specified, but it should give you the idea):

SQL Fiddle

Query 1:

SELECT CURRENT_DATE - INTERVAL '1' DAY,
       CURRENT_DATE - 1
FROM   DUAL

Results:

| CURRENT_DATE-INTERVAL'1'DAY |             CURRENT_DATE-1 |
|-----------------------------|----------------------------|
|  November, 12 2015 12:28:40 | November, 12 2015 12:28:40 |

---

Related Topics