Re: Date calculation functions : Informix bug?

>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>