Subtracting dates from one another
A date can be subtracted from another date. The result is the difference between those dates measured in (fractional) units of days.
Human readable representation of a date difference
Such a difference can be represented with a variation of the outlined idea below:
with da as (
select
81 + -- days
7/24 + -- hours
25/24/60 + -- minutes
38/24/60/60 -- seconds
as ys
from dual
)
select
da.ys,
--
trunc(da.ys) || ' days ' ||
to_char(
trunc(sysdate) +
mod(da.ys, 1) , -- Add date difference's fractional part to start of 'today'
'hh24 "hours" mi "minutes" ss "seconds"'
) humanly_readable
from
da;
The value of humanly_readable of the previous query is: 81 days 07 hours 25 minutes 38 seconds.
Convert a date to a Julian day
A date can be converted to a
Julian day using the format model
J:
select
to_number(to_char(sysdate, 'J')) jd
from
dual;
This method is wrong for dates prior to the year 0!
Special dates etc.
The minimal date is date '-4712-01-01', the maximal date is date '9999-12-31'.
Date literals between date '1582-10-05' and date '1582-10-15' evaluate to date '1582-10-15 (which corresponds to the julian day 2299161).
There is no year 0, but Oracle calculates Julian days within year 0 (bug 106242).
select
to_number(to_char(date '-4712-01-01', 'J')) julian_day_1, -- = 1
--
to_number(to_char(date '-0001-12-31', 'J')) jd_1721057,
to_number(to_char(date '+0001-01-01', 'J')) jd_1721424,
--
date '1582-10-04' "1582-10-04",
date '1582-10-05' "1582-10-15 (1st missing date)",
date '1582-10-14' "1582-10-15 (also missing date)",
date '1582-10-15' "1582-10-15 (1st gregorian day)",
date '9999-12-31' "9999-12-31" -- maximum date
from
dual;
Leap years
Oracle recognizes leap years before the year 1582:
select
to_char(date '+1200-02-28', 'J'),
to_char(date '+1200-02-29', 'J'),
to_char(date '+1200-03-01', 'J'),
--
to_char(date '+1300-02-28', 'J'),
to_char(date '+1300-02-29', 'J'),
to_char(date '+1300-03-01', 'J'),
--
to_char(date '+1400-02-28', 'J'),
to_char(date '+1400-02-29', 'J'),
to_char(date '+1400-03-01', 'J'),
--
to_char(date '+1500-02-28', 'J'),
to_char(date '+1500-02-29', 'J'),
to_char(date '+1500-03-01', 'J'),
--
to_char(date '+1580-02-28', 'J'),
to_char(date '+1580-02-29', 'J'),
to_char(date '+1580-03-01', 'J'),
--
to_char(date '+1582-02-28', 'J'),
-- to_char(date '+1582-02-29', 'J'),
to_char(date '+1582-03-01', 'J')
from
dual;
Internal representation of a date
Oracle uses 7 bytes to store a date. These bytes correspond to a date's century, the last two digits of the year, month, day, hour, minute and second.
The year is calculated with (ByteCentury-100)*100 + ByteYear-100.
In order to get the actual values for hour, minute and seconds, the value 1 must be subtracted from the corresponding byte value.
The function
dump allows to print the internal representation of a value:
create table tq84_dt(a date);
insert into tq84_dt values (timestamp '1950-04-08 12:16:20');
commit;
select dump(a) from tq84_dt;
--
-- Typ=12 Len=7: 119,150,4,8,13,17,21
--
drop table tq84_dt;
External datatype 13 vs internal datatype 12
Note how the data type number and length of a
date which
is not stored in a datafile (such as the value evaluated by
sysdate) is different from a
date which
is stored in a datafile:
create table tq84_t(dt date);
insert into tq84_t values (sysdate);
select dump(dt ) from tq84_t union all
select dump(sysdate) from dual;
--
-- DUMP(DT)
-- -----------------------------------
-- Typ=12 Len=7: 120,124,2,16,18,11,16
-- Typ=13 Len=8: 232,7,2,16,17,10,15,0
drop table tq84_t;
The «external data» type 13 is an «internal c-struct» whose length varies depending on the compiler's representation of the structure.
Type 13 is mainly used for date related calculations in
PL/SQL.