Regarding "dates are not traditional numbers", and "precision" of DATEs, which you have mentioned in your question: Oracle's DATE datatype allows us to do all sorts of calculations. Consulting Kyte & Kuhn's "Expert Oracle Database Architecture 3rd Ed." p546ff, we find that "DATE is a fixed-width 7 byte date/time datatype". Its components are: century, year, month, day, hour, minute, second (one byte for each value). This can be made visible as follows:
-- test table and data
create table dt (
what varchar2(32)
, x date
);
insert into dt (what, x) values (
'original'
, to_date ( '12-DEC-2017 12:24:36', 'dd-mon-yyyy hh24:mi:ss' )
);
insert into dt (what, x)
select 'minute', trunc( x, 'mi' ) from dt
union all
select 'day', trunc( x, 'dd' ) from dt
union all
select 'month', trunc( x, 'mm' ) from dt
union all
select 'year', trunc( x, 'y' ) from dt
;
select what, x, dump( x, 10 ) d from dt;
WHAT X D
original 12-DEC-17 Typ=12 Len=7: 120,117,12,12,13,25,37
minute 12-DEC-17 Typ=12 Len=7: 120,117,12,12,13,25,1
day 12-DEC-17 Typ=12 Len=7: 120,117,12,12,1,1,1
month 01-DEC-17 Typ=12 Len=7: 120,117,12,1,1,1,1
year 01-JAN-17 Typ=12 Len=7: 120,117,1,1,1,1,1
There's much more to be said about this, but we have to move on ...
If you need to work with a date range eg 1985-01-15 - 2017-12-01, you could use the Julian day ( the number of days since January 1, 4712 BC. Number specified with J must be integers. See also: https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00210 )
eg
select
to_char(to_date('1985-01-15','YYYY-MM-DD'),'J') "1985-01-15"
, to_char(to_date('2017-12-01','YYYY-MM-DD'),'J') "2017-12-01"
from dual;
1985-01-15 2017-12-01
2446081 2458089
Adding seconds/minutes/hours to a date can be done via fractions of a Julian day, using x/(24*60*60) and x/(24*60) and x/24, respectively. An example with random days (same range as before):
delete from dt;
declare
random_julian pls_integer := 0 ;
begin
for ctr in 10 .. 60 -- counter: ctr
loop
if mod( ctr, 12 ) = 0 then -- skip some values (increment: 12)
random_julian := trunc( dbms_random.value( 2446081, 2458089 ) ) ;
insert into dt ( what, x )
values ( 'date_' || ctr || ' unaltered', to_date ( random_julian, 'J' )) ;
insert into dt ( what, x ) values (
'date_' || ctr || ' +'|| ctr || ' seconds'
, to_date ( random_julian, 'J' ) + ctr/(24*60*60)
);
insert into dt ( what, x ) values (
'date_' || ctr || ' +'|| ctr || ' minutes'
, to_date ( random_julian, 'J' ) + ctr/(24*60)
);
insert into dt ( what, x ) values (
'date_' || ctr || ' +'|| ctr || ' hours'
, to_date ( random_julian, 'J' ) + ctr/(24)
);
insert into dt ( what, x ) values (
'date_' || ctr || ' +'|| ctr || ' days'
, to_date ( random_julian, 'J' ) + ctr
);
end if;
end loop;
end;
/
The dt table may now contain something like ...
-- same date, 3 different formats
select
what
, to_char( x, 'YYYY-MM-DD HH24:MI:SS' ) date_ymdhms
, to_char( x, 'YYYY-MM-DD' ) date_iso
, to_char( x, 'MONTH, DD YYYY' ) date_us
from dt ;
WHAT DATE_YMDHMS DATE_ISO DATE_US
date_12 unaltered 1997-12-28 00:00:00 1997-12-28 DECEMBER , 28 1997
date_12 +12 seconds 1997-12-28 00:00:12 1997-12-28 DECEMBER , 28 1997
date_12 +12 minutes 1997-12-28 00:12:00 1997-12-28 DECEMBER , 28 1997
date_12 +12 hours 1997-12-28 12:00:00 1997-12-28 DECEMBER , 28 1997
date_12 +12 days 1998-01-09 00:00:00 1998-01-09 JANUARY , 09 1998
date_24 unaltered 2016-02-16 00:00:00 2016-02-16 FEBRUARY , 16 2016
date_24 +24 seconds 2016-02-16 00:00:24 2016-02-16 FEBRUARY , 16 2016
date_24 +24 minutes 2016-02-16 00:24:00 2016-02-16 FEBRUARY , 16 2016
date_24 +24 hours 2016-02-17 00:00:00 2016-02-17 FEBRUARY , 17 2016
date_24 +24 days 2016-03-11 00:00:00 2016-03-11 MARCH , 11 2016
...
Coming back to your original question:
How can I multiply and divide dates, without losing precision?
If "days" are "precise enough" ie you do not need fractions of Julian days:
select
cond_2 - cond_1 cond_diff
, date_2 - date_1 date_diff
, ( cond_2 - cond_1 ) / ( date_2 - date_1 ) c_d
from (
select
16 cond_1 -- just hardcoded this here for demo
, 6 cond_2 -- just hardcoded this here for demo
, min( to_number( to_char( x, 'J' ) ) ) date_1
, round( avg( to_number( to_char( x, 'J' ) ) ) ) date_2
from dt
) ;
COND_DIFF DATE_DIFF C_D
---------- ---------- ----------
-10 3652 -0.00273822563
[ Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production ]
Maybe the following query will help to find the solution you are after. Test data (dbfiddle here):
-- table road_condition
COND_ID ROAD_ID COND_DATE CONDITION
1 100 01-NOV-84 18
2 100 01-JAN-09 6
3 100 19-JUN-12 4
4 100 29-APR-15 4
5 200 29-APR-92 20
6 200 05-APR-17 3
Query
select
road_id
, cond_date
, case
when x1 > 0 then x2 - x1 else 0
end days_between_i
, case
when y1 > 0 then y2 - y1 else 0
end cond_diff
, case
when x1 > 0 then round( (y2-y1 ) / ( x2-x1 ), 6 ) else 0
end slope
, case
when y1 > 0 then round( y1 - ((y2-y1)/(x2-x1)) * x1, 6 ) else 0
end y_intercept
from (
select
cond_id
, road_id
, cond_date
-- inspection days and "previous" inspection days
, iday x2
, lag( iday, 1, 0 ) over ( partition by road_id order by cond_date ) x1
-- conditions and "previous" conditions
, condition y2
, lag( condition, 1, 0 ) over ( partition by road_id order by cond_date ) y1
from (
select
cond_id
, cond_date
, to_number( to_char( cond_date, 'J' ) ) iday -- Julian day
, road_id
, condition
from road_condition
) result1
) result2 ;
Result (including result sets of subqueries):
-- final
ROAD_ID COND_DATE DAYS_BETWEEN_I COND_DIFF SLOPE Y_INTERCEPT
100 01-NOV-84 0 0 0 0
100 01-JAN-09 8827 -12 -0.001359 3343.260224
100 19-JUN-12 1265 -2 -0.001581 3887.158893
100 29-APR-15 1044 0 0 4
200 29-APR-92 0 0 0 0
200 05-APR-17 9107 -17 -0.001867 4591.05677
-- result2
COND_ID ROAD_ID COND_DATE X2 X1 Y2 Y1
1 100 01-NOV-84 2446006 0 18 0
2 100 01-JAN-09 2454833 2446006 6 18
3 100 19-JUN-12 2456098 2454833 4 6
4 100 29-APR-15 2457142 2456098 4 4
5 200 29-APR-92 2448742 0 20 0
6 200 05-APR-17 2457849 2448742 3 20
-- result1
COND_ID COND_DATE IDAY ROAD_ID CONDITION
1 01-NOV-84 2446006 100 18
2 01-JAN-09 2454833 100 6
3 19-JUN-12 2456098 100 4
4 29-APR-15 2457142 100 4
5 29-APR-92 2448742 200 20
6 05-APR-17 2457849 200 3