Re: Date calculation functions : Informix bug?

Jonathan Leffler (johnl@informix.com) wrote: : >From: rferdy@kuwait.net (Rudy Fernandes) : >Date: Sun, 20 Oct 96 09:41:44 GMT : >X-Informix-List-Id: <news.29519> : > : >In article <5408pv$i9u@cssun.mathcs.emory.edu>, jparker@boi.hp.com (Jack Parker) wrote: : >> ##################################################################### : >> FUNCTION Add_Month (bdate, num_months) : >> : >> DEFINE bdate, edate DATE, num_months SMALLINT : >> : >> LET edate = bdate + num_months UNITS MONTH : >> RETURN edate : >> : >> END FUNCTION : > : >I am running Informix 4.1 tools at our site and we, some six months back, : >joyfully changed our date computation code to use syntax similar to the : >above. : > : >Unfortunately it does not work. : >e.g '31/10/96 + 1 UNITS MONTH' gives an error : >(Form 1267 : The result of a datetime computation is out of range) : >Informix seems to be trying to return 31/11/96 : > : >The same problem occurs with UNITS YEAR if a leap year is involved. : > : >I'm curious to know if this is a version 4.1 problem alone. : No, it is not a problem in version 4.1 only -- it is a generic problem. : I've argued before, and am going to argue again, that the problem is really : that it should never work, because the operation requested is not : unambiguously defined for all input values. : If you look at the description of INTERVAL types, you will see that there : are two distinct, never-to-be-mixed groups of interval types, namely those : involving year and month, and those involving days and smaller time units. : There is a sound reason why these cannot be mixed -- arithmetic doesn't : work reliably. : Now, in the expression given, we are mixing a DATE and an INTERVAL MONTH TO : MONTH. The computation therefore converts the DATE to DATETIME YEAR TO : DAY, and we then run into a grey area in the description of DATETIME and : INTERVAL arithmetic. In my view, you cannot mix INTERVAL MONTH TO MONTH : values with a DATETIME which includes {YEAR and/or MONTH} with DAY, : basically for the same reason that you can't mix INTERVAL MONTH TO MONTH : and INTERVAL DAY TO DAY. However, this restriction is not enforced by the : Informix datetime arithmetic package, but the side effect of this not being : enforced is that the calculations can break down whenever the date falls : after the 28th of the month. : For example: : 29 October 1996 + 4 UNITS MONTH : 29 November 1996 + 3 UNITS MONTH : 31 October 1996 + 1 UNITS MONTH : 29 February 1996 + 1 UNITS YEAR : Note that these operations are simply not defined. For some people, the : preferred answer would be the last available day of the month (eg 28th : February 1997 or 30th November 1996); for others, the preferred answer : would be 1st March 1997 or 1st December 1996. You can devise situations : where the preferred answer might even be the 2nd or 3rd of the month. : OK -- enough philosophy. What can you do to avoid problems? : 1. Do not mix "n UNITS MONTH" or "n UNITS YEAR" intervals with DATE : types. This is imperative; if you don't honour this, then : calculations will work most of the time, but will fail : semi-randomly when the dates are near the end of the month and the : interval manages to hit a non-existent date. : 2. Define what behaviour you really want when you add 1 month to 31st : January. Implement code which handles this correctly, in both leap : years and non leap years. You will typically do this by splitting : the date into YEAR, MONTH and DAY components. You can do interval : arithmetic on the YEAR and MONTH parts. You then decide what to do : about the DAY bits, using your definition. Or you can simply add a : fixed number of days to the DATE (eg 30 UNITS DAYS for 1 month, 91 : UNITS DAYS for 3 months, etc). : Eg: (incomplete code, but not by much; untested in this incarnation) : FUNCTION Add_Month (bdate, num_months) : DEFINE bdate DATE : DEFINE num_months SMALLINT : DEFINE edate DATE : DEFINE yy, mm, dd SMALLINT : DEFINE x SMALLINT : LET yy = YEAR(bdate) : LET mm = MONTH(bdate) + num_months : LET dd = DAY(bdate) : IF (mm > 12) THEN : LET x = (mm - 1) / 12 : LET yy = yy + x : LET mm = mm - (12 * x) : END IF : IF (mm <= 0) THEN : -- This needs sitting down and thinking about... : LET x = (????) / 12 : LET yy = yy - x : LET mm = mm + (12 * x) : END IF : -- Assume that you want the last day of the relevant month... : IF dd > 28 THEN : IF (dd > 30) AND (mm = 4 OR mm = 6 OR mm = 9 OR mm = 11) THEN : -- Thirty days hath November, : -- April, June and September. : LET dd = 30 : END IF : IF mm = 2 THEN : IF isleap(yy) THEN : LET dd = 29 : ELSE : LET dd = 28 : END IF : END IF : END IF : LET edate = MDY(mm, dd, yy) : RETURN edate : END FUNCTION : -- Accurate for dates from 1753 except in Russia : FUNCTION isleap(yy) : DEFINE yy INTEGER : CASE : WHEN yy MOD 400 = 0 RETURN TRUE : WHEN yy MOD 100 = 0 RETURN FALSE : WHEN yy MOD 4 = 0 RETURN TRUE : OTHERWISE RETURN FALSE : END CASE : END FUNCTION : Yours, : Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> Following is a stored procedure that increments/decrements a date by a specified number of months, returning the last day of the computed month if the [in|de]cremented date is invalid. This particular procedure deals with a DATE variable; you can tune it to deal with a DATETIME variable and do other magical things if you wish. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- CREATE PROCEDURE incr_date(pBeg_date DATE, pIncr_mos INTEGER) RETURNING DATE; -- Increment or decrement a date by a specified number of months. If the -- computed date is beyond the last day of the month, return the last day -- of the month. DEFINE pComp_date DATE; DEFINE pAdj_days SMALLINT; BEGIN LET pAdj_days = 0; WHILE 1 = 1 -- If the computed day is beyond the last day of the month, -- then subtract one day at a time until we find the last day -- of that month. ON EXCEPTION IN (-1267) LET pAdj_days = pAdj_days + 1; END EXCEPTION LET pComp_date = DATE (EXTEND (pBeg_date, YEAR TO DAY) - pAdj_days UNITS DAY + pIncr_mos UNITS MONTH); EXIT WHILE; END WHILE RETURN pComp_date; END END PROCEDURE; -- ___ ___ Principal Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own. Eventually all things merge into one...and a river runs through it. -N.Maclean