The concept of age seems quite simple.
Right now I'm 42 years old.
I can be more precise and define it as 42 years, some months and several days.
And at a given date I will be 43 years old, and months and date are reset to 0.
Unfortunately it's hard to calculate with these numbers.
For those who wants to go into detail I'd recommend ISO 8601 Data elements and interchange formats. Unfortunately I don't have access to this document.
Another good reason is the explanation for CPANs DateTime.pm module.
I tried a slightly different approach:
Define the age as integer and fractional part, where the integer part is the age in years as we are used to it, and the fractional part is the number of days already passed divided by the total days in the current year.This sounds quite simple, but it has some complicated effects due to leap years. So I wrote a little package ( see below) to handle these effects.
The Package is called BX_YEAR_CALC and has 2 functions:
DIFFERENCE (year1 date, year2 date) returns the AGE.
ADDITION (year1 date, diff number) returns the date at a specific AGE.
Here are some examples:
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
SQL> select bx_year_calc.difference('2004-02-29 12:00:00', '2005-03-01 11:59:00') diff from dual;
DIFF
----------
1.00136796
SQL> select bx_year_calc.addition ('2004-02-29 12:00:00', 1.00136796042617960426179604261796042618) d from dual;
D
-------------------
2005-03-01 11:59:00
SQL> select bx_year_calc.addition ('2005-03-01 11:59:00', -1.00136796042617960426179604261796042618) d from dual;
D
-------------------
2004-03-01 00:00:00
You can see in these examples there are situations - especially around leap years and Feb 29th - where the calculation goes quite wrong. My implementation tries to reduce this effect to a minimum.
Feel free to play with dates and ages, any comments are very welcome.
create or replace PACKAGE BX_YEAR_CALC AS
/* 2018-03-25 - berx - initial - martin.a.berger@gmail.com
BX_YEAR_CALC - to calculate DATES in units or YEARS
equal idea as DATE1 - DATE2 returns a number,
where the integer part are full days and fractional part reflects remaining hours, minutes and seconds
As some years has 365 or 366 days, the last year can have different number of total days,
so the fraction must be calculated based on this.
Only the number of days of the last year (based on the begin) is calculated.
Be aware: there are situations with leap years or gregorian calendar where results are not intuitive.
If you find a bette rimplementation for a given situation, let's discuss
*/
/* returns the "age in years" between the 2 parameters.
integer is quite obvious - it's the years between the start date and the same day (if it exists) in target year.
fraction is remaining days divided by days in the last year
*/
FUNCTION DIFFERENCE(YEAR1 date default sysdate,
YEAR2 date default sysdate)
RETURN number;
/* adds (or substracts - if number is negative) a "age in years" as defined by DIFFERENCE to a given date
for addition, first the integer part is added and then the fraction - based on days in last year.
for subtraction, first the fraction is removed (to be more aligned to addition) and the the integer
*/
FUNCTION ADDITION (YEAR1 date default sysdate,
DIFF number default 0)
RETURN date;
/* returns the next "existing" date to a given "old-date" and a specific other year ("new year")
this handles the situation similar to "today is 2004-02-29. what's the date a year ago?
to avoid ORA-01840: input value not long enough for date format
this function searches the next day "above" (with incr => default 1) or "below" (with incr => -1)
other values for incr are not defined.
*/
FUNCTION next_real_date (
new_year VARCHAR2,
old_date DATE,
incr number default 1
)
return date;
END BX_YEAR_CALC;
CREATE OR REPLACE PACKAGE BODY bx_year_calc AS
c_debug NUMBER := 0;
PROCEDURE debug (
message VARCHAR2
)
AS
BEGIN
IF
c_debug > 0
THEN
dbms_output.put_line(message);
END IF;
END debug;
FUNCTION next_real_date (
new_year VARCHAR2,
old_date DATE,
incr NUMBER DEFAULT 1
) RETURN DATE AS
year2_year NUMBER;
return_date DATE; -- the highest full year related to YEAR1 which is smaller than YEAR2
year_diff NUMBER;
leap_help BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
leap_count NUMBER := 0; -- how many days to add to fix leap issue
date_not_valid EXCEPTION;
PRAGMA exception_init ( date_not_valid,-1839 );
BEGIN
if NOT abs(incr)=1 then
raise_application_error( -20001, ' incr msut be +1 or -1 ' );
end if;
WHILE NOT leap_help LOOP
BEGIN
IF
leap_count = 0
THEN
return_date := TO_DATE(new_year || '-' || TO_CHAR(old_date,'MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');
leap_help := true;
ELSE
return_date := TO_DATE(new_year || '-' || TO_CHAR(old_date + leap_count,'MM-DD'),'YYYY-MM-DD');
leap_help := true; -- this happens only if NO exception is thrown
END IF;
EXCEPTION
WHEN date_not_valid -- ORA-01839: date not valid for month specified
THEN
leap_count := leap_count + incr;
IF -- just a safety in case anything goes wrong
abs(leap_count) > 33
THEN
leap_count := 1 / 0;
END IF;
END;
END LOOP;
RETURN return_date;
END next_real_date;
FUNCTION difference (
year1 DATE,
year2 DATE DEFAULT SYSDATE
) RETURN NUMBER AS
days_lastyear NUMBER; -- how many days the last year has
fract_lastyear NUMBER; -- remaining part as fraction of a year
year2_year NUMBER;
hi_year_lo DATE; -- the highest full year related to YEAR1 which is smaller than YEAR2
hi_year_hi DATE; -- the smallest full year related to YEAR1 which is higher than YEAR2
year_diff NUMBER;
leap_help BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
leap_count NUMBER := 0; -- how many days to add to fix leap issue
date_not_valid EXCEPTION;
PRAGMA exception_init ( date_not_valid,-1839 );
BEGIN
IF
year1 > year2
THEN
RETURN difference(year2,year1);
ELSE
year2_year := to_number(TO_CHAR(year2,'YYYY') );
hi_year_lo := next_real_date(TO_CHAR(year2_year,'0000'),year1);
hi_year_hi := next_real_date(TO_CHAR(year2_year + 1,'0000'),year1);
-- how many days are in the last year to take care of
days_lastyear := hi_year_hi - hi_year_lo;
fract_lastyear := ( year2 - hi_year_lo ) / days_lastyear;
year_diff := to_number(TO_CHAR(hi_year_lo,'YYYY') ) - to_number(TO_CHAR(year1,'YYYY') );
RETURN year_diff + fract_lastyear;
END IF;
END difference;
FUNCTION addition (
year1 DATE DEFAULT SYSDATE,
diff NUMBER
) RETURN DATE AS
year_year NUMBER;
year_diff NUMBER;
days_lastyear NUMBER; -- how many days the last year has
hi_year_lo DATE; -- the highest full year related to YEAR1 which is smaller than YEAR2
hi_year_hi DATE; -- the smallest full year related to YEAR1 which is higher than YEAR2
lo_year_lo DATE; -- a temp "lowest date" - only to keep the calculation somehow readable
days_jump_back NUMBER := 15; -- a number bigger than "365 + (15-4)" - google gregorian calendar reform 1582 & 4 October 1582
days_offset NUMBER;
leap_help BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
leap_count NUMBER := 0; -- how many days to add to fix leap issue
date_not_valid EXCEPTION;
PRAGMA exception_init ( date_not_valid,-1839 );
BEGIN
IF
diff < 0
THEN
-- first let's substract only the fraction of diff
year_year := to_number(TO_CHAR(year1,'YYYY') );
hi_year_lo := next_real_date(TO_CHAR(year_year - 1,'0000'),year1);
days_lastyear := year1 - hi_year_lo;
hi_year_hi := hi_year_lo + ( days_lastyear * ( diff - trunc(diff) ) );
-- now the easy part - years
RETURN next_real_date(TO_CHAR(to_number(TO_CHAR(hi_year_hi,'YYYY') ) + trunc(diff) + 1,'0000'),hi_year_hi);
-- trunc(diff)+1 is required, as we substracted "-1" in the calculation of "hi_year_lo" above already
ELSIF diff = 0 THEN
RETURN year1;
ELSE
year_year := to_number(TO_CHAR(year1,'YYYY') ) + trunc(diff);
hi_year_lo := next_real_date(TO_CHAR(year_year,'0000'),year1);
hi_year_hi := next_real_date(TO_CHAR(year_year + 1,'0000'),year1);
--
days_lastyear := hi_year_hi - hi_year_lo;
RETURN hi_year_lo + ( days_lastyear * ( diff - trunc(diff) ) );
END IF;
END addition;
END bx_year_calc;
Keine Kommentare:
Kommentar veröffentlichen