Leap Year Issue

Hello, Wonder if you guys are also experiencing this error. All My queries running something like this are failing select today - 2 units year from systables Workaround is failry simple and now are combing our code to see if we can change it for something like this select today - (2 * 365) from systables We had this issue 8 years ago too, but it was fixed on version 10. Now this is happening on 11.1, 11.5 and 11.7. It is weird how bugs became alive after a while. Zombie Bugs! Just a couple of hours ago a PMR was opened with IBM but I have not heard anything back yet. I suspect they are furiously writing a FixPack or may be let the day pass ... Walter

On Wed, Feb 29, 2012 at 11:20, WALTER MILAN <walter.d.milan@gmail.com>wrote: > Wonder if you guys are also experiencing this error. All My queries running > something like this are failing > > select today - 2 units year from systables > This is the way that Informix has worked since DATETIME was introduced in version 4.00 back in about 1988. Two years ago, there was no 29th of February, and the error is telling you that. You get similar results with subtracting a number of months for any date from the 29th-31st of a month too. Mostly, subtracting years works fine - but Leap Day is the exception (when the number of years subtracted is not a multiple of 4, and even that could run into problems if the target year is 1700, 1800, 1900, 2100, 2200, 2300 etc which were not themselves leap years, or will not be leap years). Workaround is fairly simple and now are combing our code to see if we can > change it for something like this > > select today - (2 * 365) from systables > > We had this issue 8 years ago too, but it was fixed on version 10. Now > this is > happening on 11.1, 11.5 and 11.7. > > It is weird how bugs became alive after a while. Zombie Bugs! Just a > couple of > hours ago a PMR was opened with IBM but I have not heard anything back > yet. I > suspect they are furiously writing a FixPack or may be let the day pass ... > What do you mean 'it was fixed in 10.00'? Do you have a bug number to justify that assertion? TTBOMK, it has never been fixed, though the issue has been raised once more in the last few weeks. Note that one of the many problems with not generating an error is that you can end up with different answers depending on how the SQL optimizer chooses to process: (DATETIME(2012-02-29) - 2 UNITS YEAR) + 2 UNITS YEAR If it observes that the the constant subtracted and added are the same, it might eliminate that calculation, giving you a result 2012-02-29. If it is fixed to give DATETIME(2010-02-28) as the result of subtracting 2 years, then the addition gives you 2012-02-28. So the double computation gives a different answer from what you get by omitting 'adding 0' to a date. This is at best confusing. The error is much the safest way to do business. That said, customer pressure may end up requiring we change 20+ years of behaviour, but that opens up the absurdities of ((x - y) + y) != x. (The problem is actually more extreme for months: (DATETIME(2012-05-31) - 1 UNITS MONTH) + 1 UNITS MONTH == 2012-05-30 (DATETIME(2012-05-31) - 2 UNITS MONTH) + 2 UNITS MONTH == 2012-05-31 (DATETIME(2012-05-31) - 3 UNITS MONTH) + 3 UNITS MONTH == 2012-05-29 (DATETIME(2011-05-31) - 3 UNITS MONTH) + 3 UNITS MONTH == 2011-05-28 Etcetera.) -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04088d8bc2c29904ba203f72

Hi Jonathan, As soon as I sent the email, my fellow dba told me I was wrong regarding to this being fixed on 10. I just trusted the developer's word. It caught me with a lower guard, because I always question what they told me. Thanks Walter

Date arithmetic is really awkward. For example, what should engine return for Feb 28 2011 + 1 year Feb 28 2012? Feb 29 2012? Or what engine should return for Jan 31 2012 + 1 month? Feb 28 2012? Then what should return Jan 28 2012 + 1 month? Feb 25 2012? (it is 3 days before last day). And so on..... Regards Hrvoje On 29.02.2012. 20:20, WALTER MILAN wrote: > Hello, > > Wonder if you guys are also experiencing this error. All My queries running > something like this are failing > > select today - 2 units year from systables > > Workaround is failry simple and now are combing our code to see if we can > change it for something like this > > select today - (2 * 365) from systables > > We had this issue 8 years ago too, but it was fixed on version 10. Now this is > happening on 11.1, 11.5 and 11.7. > > It is weird how bugs became alive after a while. Zombie Bugs! Just a couple of > hours ago a PMR was opened with IBM but I have not heard anything back yet. I > suspect they are furiously writing a FixPack or may be let the day pass ... > > Walter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

BTW root@ubuntu804:~# echo "select DBINFO('version','full') from systables where tabid=1; select today - 2 units year from systables where tabid=1;" | dbaccess sysmaster Database selected. (constant) IBM Informix Dynamic Server Version 10.00.UC9 1 row(s) retrieved. (expression) 1267: The result of a datetime computation is out of range. Error in line 1Near character position 116 Database closed. It is working as usual in v10 I think the same result is in some other RDBMS Hrvoje On 29.02.2012. 20:20, WALTER MILAN wrote: > Hello, > > Wonder if you guys are also experiencing this error. All My queries running > something like this are failing > > select today - 2 units year from systables > > Workaround is failry simple and now are combing our code to see if we can > change it for something like this > > select today - (2 * 365) from systables > > We had this issue 8 years ago too, but it was fixed on version 10. Now this is > happening on 11.1, 11.5 and 11.7. > > It is weird how bugs became alive after a while. Zombie Bugs! Just a couple of > hours ago a PMR was opened with IBM but I have not heard anything back yet. I > suspect they are furiously writing a FixPack or may be let the day pass ... > > Walter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

I worked at a place in the early 90s (Informix 4.10) where a group had written a 4gl application that looked at the last 6 months sales. They found out the hard way that "today - 6 months" doesn't work very good for August 31st. Their app errored out spectacularly in almost 100 stores. Bob ----- Original Message ----- From: "Hrvoje Zokovic" <hzokovic.iiug@gmail.com> To: ids@iiug.org Sent: Wednesday, February 29, 2012 4:14:22 PM Subject: Re: Leap Year Issue [26407] Date arithmetic is really awkward. For example, what should engine return for Feb 28 2011 + 1 year Feb 28 2012? Feb 29 2012? Or what engine should return for Jan 31 2012 + 1 month? Feb 28 2012? Then what should return Jan 28 2012 + 1 month? Feb 25 2012? (it is 3 days before last day). And so on..... Regards Hrvoje On 29.02.2012. 20:20, WALTER MILAN wrote: > Hello, > > Wonder if you guys are also experiencing this error. All My queries running > something like this are failing > > select today - 2 units year from systables > > Workaround is failry simple and now are combing our code to see if we can > change it for something like this > > select today - (2 * 365) from systables > > We had this issue 8 years ago too, but it was fixed on version 10. Now this is > happening on 11.1, 11.5 and 11.7. > > It is weird how bugs became alive after a while. Zombie Bugs! Just a couple of > hours ago a PMR was opened with IBM but I have not heard anything back yet. I > suspect they are furiously writing a FixPack or may be let the day pass ... > > Walter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Try this ISQL: 1. select mdy(month(today),1,year(today)) - 6 units month + (day(today)-1) units day from yr table where yr where_clause 2. Or in *4GL: let new_date_6months_before (date type) = mdy(month(today),1,year(today)) - 6 units months + (day(today)-1) units day If today is "31/08/2012" then 6 months ago the date would be "02/03/2012" as 2012 is a leap year which has 29 days in February while the resultant day is 1 + 30 (day(31/08/2012) -1 ) = 31 days which is 31 - 29 = 2 days over February 2012. So the final result is "02/03(02+1)/2012". if the result you want is 29/02/2012 then you have to massge a bit in yr 4GL: let chk_date = new_date_6months_before + 1 if day(chk_date) <> 1 then let new_date_6months_before = mdy(month(new_date_6months_before),1, year(new_date_6months_before)) -1 #display new_date_6months_before "31/08/2012" shows "29/02/2012" end if From: "rroussey@comcast.net" <rroussey@comcast.net> To: ids@iiug.org Sent: Thursday, 1 March 2012 8:58 AM Subject: Re: Leap Year Issue [26409] I worked at a place in the early 90s (Informix 4.10) where a group had written a 4gl application that looked at the last 6 months sales. They found out the hard way that "today - 6 months" doesn't work very good for August 31st. Their app errored out spectacularly in almost 100 stores. Bob ----- Original Message ----- From: "Hrvoje Zokovic" <hzokovic.iiug@gmail.com> To: ids@iiug.org Sent: Wednesday, February 29, 2012 4:14:22 PM Subject: Re: Leap Year Issue [26407] Date arithmetic is really awkward. For example, what should engine return for Feb 28 2011 + 1 year Feb 28 2012? Feb 29 2012? Or what engine should return for Jan 31 2012 + 1 month? Feb 28 2012? Then what should return Jan 28 2012 + 1 month? Feb 25 2012? (it is 3 days before last day). And so on..... Regards Hrvoje On 29.02.2012. 20:20, WALTER MILAN wrote: > Hello, > > Wonder if you guys are also experiencing this error. All My queries running > something like this are failing > > select today - 2 units year from systables > > Workaround is failry simple and now are combing our code to see if we can > change it for something like this > > select today - (2 * 365) from systables > > We had this issue 8 years ago too, but it was fixed on version 10. Now this is > happening on 11.1, 11.5 and 11.7. > > It is weird how bugs became alive after a while. Zombie Bugs! Just a couple of > hours ago a PMR was opened with IBM but I have not heard anything back yet. I > suspect they are furiously writing a FixPack or may be let the day pass ... > > Walter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Or just today-180 ? Probably close enough for 99% of cases.. On 1 Mar 2012 05:28, "Long Nguyen" <longhuynguyen51@yahoo.com.au> wrote: > Try this ISQL: > 1. select mdy(month(today),1,year(today)) - 6 units month + (day(today)-1) > units day > from yr table where yr where_clause > > 2. Or in *4GL: > > let new_date_6months_before (date type) = mdy(month(today),1,year(today)) > - 6 > units months + (day(today)-1) units day > > If today is "31/08/2012" then 6 months ago the date would be "02/03/2012" > as > 2012 is a leap year which has 29 days in February while the resultant day > is 1 > + 30 (day(31/08/2012) -1 ) = 31 days > which is 31 - 29 = 2 days over February 2012. So the final result is > "02/03(02+1)/2012". > > if the result you want is 29/02/2012 then you have to massge a bit in yr > 4GL: > > let chk_date = new_date_6months_before + 1 > if day(chk_date) <> 1 then > > let new_date_6months_before = mdy(month(new_date_6months_before),1, > > year(new_date_6months_before)) -1 > > #display new_date_6months_before "31/08/2012" shows "29/02/2012" > end if > > From: "rroussey@comcast.net" <rroussey@comcast.net> > To: ids@iiug.org > Sent: Thursday, 1 March 2012 8:58 AM > Subject: Re: Leap Year Issue [26409] > > I worked at a place in the early 90s (Informix 4.10) where a group had > written > a 4gl application that looked at the last 6 months sales. They found out > the > hard way that "today - 6 months" doesn't work very good for August 31st. > Their > app errored out spectacularly in almost 100 stores. > > Bob > > ----- Original Message ----- > From: "Hrvoje Zokovic" <hzokovic.iiug@gmail.com> > To: ids@iiug.org > Sent: Wednesday, February 29, 2012 4:14:22 PM > Subject: Re: Leap Year Issue [26407] > > Date arithmetic is really awkward. > For example, what should engine return for > Feb 28 2011 + 1 year > Feb 28 2012? > Feb 29 2012? > Or what engine should return for > Jan 31 2012 + 1 month? > Feb 28 2012? > Then what should return > Jan 28 2012 + 1 month? > Feb 25 2012? (it is 3 days before last day). > And so on..... > > Regards > Hrvoje > > On 29.02.2012. 20:20, WALTER MILAN wrote: > > Hello, > > > > Wonder if you guys are also experiencing this error. All My queries > running > > something like this are failing > > > > select today - 2 units year from systables > > > > Workaround is failry simple and now are combing our code to see if we can > > change it for something like this > > > > select today - (2 * 365) from systables > > > > We had this issue 8 years ago too, but it was fixed on version 10. Now > this > is > > happening on 11.1, 11.5 and 11.7. > > > > It is weird how bugs became alive after a while. Zombie Bugs! Just a > couple > of > > hours ago a PMR was opened with IBM but I have not heard anything back > yet. > I > > suspect they are furiously writing a FixPack or may be let the day pass > ... > > > > Walter > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502caea05296b04ba29c985

The original implementation from version 4.0 followed the ANSI spec for Datetime at the time. Year-month interval arithmetic is fundamentally different from date-time interval arithmetic. select today - 2 units year from systables should produce an error if it would produce an invalid result (only possible when today is a leap day). (dunno if this has changed over the years; I can't imagine why) instead of doing such an expression in your query, it should be done in procedural code so you can test for bogus math results and make your own result suitable to your needs (only you can how you want "X + 1 units month" interpreted when X is May 31 -- do you want June 30 or July 1?) From: WALTER MILAN Sent: 02/29/12 11:20 AM To: ids@iiug.org Subject: Leap Year Issue [26404] Hello, Wonder if you guys are also experiencing this error. All My queries running something like this are failing select today - 2 units year from systables Workaround is failry simple and now are combing our code to see if we can change it for something like this select today - (2 * 365) from systables We had this issue 8 years ago too, but it was fixed on version 10. Now this is happening on 11.1, 11.5 and 11.7. It is weird how bugs became alive after a while. Zombie Bugs! Just a couple of hours ago a PMR was opened with IBM but I have not heard anything back yet. I suspect they are furiously writing a FixPack or may be let the day pass ... Walter

This is exactly what should happen in any version. The original pre-release 4.0 implementation of datetime arithmetic was, um, unrobust as hell. I got the ANSI spec and did a bunch of ad hoc testing to shake it out, and it broke all over the place. Wrong Results bugs stopped releases dead in their tracks. ;) (Whomever wrote the original Crackle spec had clearly not read the ANSI spec in detail -- I had to author the detail of how year-month and date-time intervals differed.) The ANSI spec (back then, anyway) allowed no assumptions for year-month interval arithmetic botches. Failure was the only option. ----- Original Message ----- From: Hrvoje Zokovic Sent: 02/29/12 01:56 PM To: ids@iiug.org Subject: Re: Leap Year Issue [26408] BTW root@ubuntu804:~# echo "select DBINFO('version','full') from systables where tabid=1; select today - 2 units year from systables where tabid=1;" | dbaccess sysmaster Database selected. (constant) IBM Informix Dynamic Server Version 10.00.UC9 1 row(s) retrieved. (expression) 1267: The result of a datetime computation is out of range. Error in line 1 Near character position 116 Database closed. It is working as usual in v10 I think the same result is in some other RDBMS Hrvoje On 29.02.2012. 20:20, WALTER MILAN wrote: > Hello, > > Wonder if you guys are also experiencing this error. All My queries running > something like this are failing > > select today - 2 units year from systables > > Workaround is failry simple and now are combing our code to see if we can > change it for something like this > > select today - (2 * 365) from systables > > We had this issue 8 years ago too, but it was fixed on version 10. Now this is > happening on 11.1, 11.5 and 11.7. > > It is weird how bugs became alive after a while. Zombie Bugs! Just a couple of > hours ago a PMR was opened with IBM but I have not heard anything back yet. I > suspect they are furiously writing a FixPack or may be let the day pass ... > > Walter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi MIke, Off course "date - no. of days" is exactly the answer of "how many days ago you want" But the boss often wants a report of say 2.5 years or 10 months ago and in his mind he seldom thinks of which month has 30 or 31 days and which year is a leap year etc... So that formula is the quickest way of calculating dates without worrying about unexpected results which might occur sometimes. ________________________________ From: Mike Aubury <iiug@aubit.com> To: ids@iiug.org Sent: Thursday, 1 March 2012 6:55 PM Subject: Re: Leap Year Issue [26414] Or just today-180 ? Probably close enough for 99% of cases.. On 1 Mar 2012 05:28, "Long Nguyen" <longhuynguyen51@yahoo.com.au> wrote: > Try this ISQL: > 1. select mdy(month(today),1,year(today)) - 6 units month + (day(today)-1) > units day > from yr table where yr where_clause > > 2. Or in *4GL: > > let new_date_6months_before (date type) = mdy(month(today),1,year(today)) > - 6 > units months + (day(today)-1) units day > > If today is "31/08/2012" then 6 months ago the date would be "02/03/2012" > as > 2012 is a leap year which has 29 days in February while the resultant day > is 1 > + 30 (day(31/08/2012) -1 ) = 31 days > which is 31 - 29 = 2 days over February 2012. So the final result is > "02/03(02+1)/2012". > > if the result you want is 29/02/2012 then you have to massge a bit in yr > 4GL: > > let chk_date = new_date_6months_before + 1 > if day(chk_date) <> 1 then > > let new_date_6months_before = mdy(month(new_date_6months_before),1, > > year(new_date_6months_before)) -1 > > #display new_date_6months_before "31/08/2012" shows "29/02/2012" > end if > > From: "rroussey@comcast.net" <rroussey@comcast.net> > To: ids@iiug.org > Sent: Thursday, 1 March 2012 8:58 AM > Subject: Re: Leap Year Issue [26409] > > I worked at a place in the early 90s (Informix 4.10) where a group had > written > a 4gl application that looked at the last 6 months sales. They found out > the > hard way that "today - 6 months" doesn't work very good for August 31st. > Their > app errored out spectacularly in almost 100 stores. > > Bob > > ----- Original Message ----- > From: "Hrvoje Zokovic" <hzokovic.iiug@gmail.com> > To: ids@iiug.org > Sent: Wednesday, February 29, 2012 4:14:22 PM > Subject: Re: Leap Year Issue [26407] > > Date arithmetic is really awkward. > For example, what should engine return for > Feb 28 2011 + 1 year > Feb 28 2012? > Feb 29 2012? > Or what engine should return for > Jan 31 2012 + 1 month? > Feb 28 2012? > Then what should return > Jan 28 2012 + 1 month? > Feb 25 2012? (it is 3 days before last day). > And so on..... > > Regards > Hrvoje > > On 29.02.2012. 20:20, WALTER MILAN wrote: > > Hello, > > > > Wonder if you guys are also experiencing this error. All My queries > running > > something like this are failing > > > > select today - 2 units year from systables > > > > Workaround is failry simple and now are combing our code to see if we can > > change it for something like this > > > > select today - (2 * 365) from systables > > > > We had this issue 8 years ago too, but it was fixed on version 10. Now > this > is > > happening on 11.1, 11.5 and 11.7. > > > > It is weird how bugs became alive after a while. Zombie Bugs! Just a > couple > of > > hours ago a PMR was opened with IBM but I have not heard anything back > yet. > I > > suspect they are furiously writing a FixPack or may be let the day pass > ... > > > > Walter > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502caea05296b04ba29c985 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Since you are on 11+ version you can use this: select add_months(datetime(2012-02-29) year to day, -24) from systables where tabid=1; (if one year is equal to 12 months :o)) Regards Hrvoje On 29.02.2012. 20:20, WALTER MILAN wrote: > Hello, > > Wonder if you guys are also experiencing this error. All My queries running > something like this are failing > > select today - 2 units year from systables > > Workaround is failry simple and now are combing our code to see if we can > change it for something like this > > select today - (2 * 365) from systables > > We had this issue 8 years ago too, but it was fixed on version 10. Now this is > happening on 11.1, 11.5 and 11.7. > > It is weird how bugs became alive after a while. Zombie Bugs! Just a couple of > hours ago a PMR was opened with IBM but I have not heard anything back yet. I > suspect they are furiously writing a FixPack or may be let the day pass ... > > Walter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

My total solution to the problems you mention was to create a date lookup fact table (50 years worth), where I define what date to return when doing a lookup. Example: if date = JAN-31-2012 then +1 month, return FEB-29-2012, +2 months return MAR-31-2012, +3 months return APR-30-2012.. or if date = FEB-29-2012 then +1 year, return FEB-28-2013, etc. I have developed a utility programs which generates informix load files for several styles of date fact tables, depending on what rules you define. The lookups against these fact tables are very fast, since 50 years worth only occupies a small amount of space, depending on how many different dates you want to store and lookup for each row. example: CREATE TABLE date_fact ( lookup_date DATE, plus1_month DATE, plus2_month DATE, plus3_month DATE, plus1_years DATE, plus2_years DATE, less1_month DATE, less2_month DATE, less3_month DATE, less1_years DATE,... ); LOAD "date_fact.ld" INSERT INTO date_fact; CREATE UNIQUE INDEX idx_lookup_date ON date_fact(lookup_date);

If you want total control of what date should be returned, My solution to the problems you mention was to create a date lookup fact table (50 years worth, or however much you need), where I define what date to return when doing a lookup. Example: if date = JAN-31-2012 then +1 month, return FEB-29-2012, +2 months return MAR-31-2012, +3 months return APR-30-2012.. or if date = FEB-29-2012 then +1 year, return FEB-28-2013, etc. I have developed a utility programs which generates informix load files for several styles of date fact tables, depending on what rules you want to establish. The lookups against these fact tables are very fast, since 50 years worth only occupies a small amount of space, depending on how many different dates you want to store and lookup for each row. example: CREATE TABLE date_fact ( lookup_date DATE, plus1_month DATE, plus2_month DATE, plus3_month DATE, plus1_years DATE, plus2_years DATE, less1_month DATE, less2_month DATE, less3_month DATE, less1_years DATE,... ); LOAD "date_fact.ld" INSERT INTO date_fact; CREATE UNIQUE INDEX idx_lookup_date ON date_fact(lookup_date);