Multiply and divide dates

Question

I had a recent DBA.SE question that involved dates that were used as the x-axis in a graph: Interpolate dates along a line.

It was centred around a road_condition table:

COND_ID ROAD_ID   COND_DATE  CONDITION
------- -------   --------   ---------
      1     100   84-11-01          18
      2     100   09-01-01           6
      3     100   12-06-19           4
      4     100   15-04-29           4

      5     200   92-04-29          20
      6     200   17-04-05           3

Note: I consider cond_date to be the X-axis, and condition to be the Y-axis.

In my an attempt to answer my own question, I tried to do things like use cond_date as X1 in a calculation:

y1 - ((y2-y1)/(x2-x1)) * x1 as y_intercept

However, obviously I can't just use a date in a calculation that involves multiplication or division; dates are not traditional numbers.

As a work around, I stripped the date of its month and day: extract(year from cond_date) and performed the calculations from there on the resulting integer (the year). However, by doing this, I of course lose valuable precision in the number. This compromises the output of the calculation.

How can I multiply and divide dates, without losing precision?

Answer 1

I think you can convert each date value to a number of time units -- days, hours, seconds etc. according to your precision requirement -- since some base date, similar to the Unix epoch. This will allow you to manipulate them as regular numbers.

For example, the expression cond_date - TO_DATE('1970-01-01', 'YYYY-MM-DD') will give you the number of days since 1 Jan. 1970.

Answer 2

Slope is a numeric concept. It does not make sense to have a slope with time unless you already have a unit for it. Fortunately we have date arithmetic in Oracle that can help you out. To do this you need to convert to a numeric quantity as you have noted.

Date arithmetic works with addition and subtraction and this can create scoped numeric representations of this for your purposes. Slope is easy enough (no modifications there since you are subtracting dates first). The intercept is more interesting. The question is at what date x = 0 is at. If you subtract that date from x1, then you will get a numeric result that you can use for your multiplication factor.

Answer 3

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