Informix date functions help

I have a date table with date column in the Informix Database. I need to have the Previous Month condition. If I run the report on Jan 1st 2006 or Jan 2nd or any date in the month of Jan 2006 I should get Dec 2005 1 to 31 data in the report. I f I run the report on Feb 1st the report should show Jan Data. The below condition helps to get the data for the previous month in Oracle Database. I need the same kind of code in Informix. TRUNC(TIME_TAB.DAY,'MON') = TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') TIME_TAB is the Time Table and DAY is the date column with date datatype.

select (extend(current, year to month) - 1 units month)::date from systables where tabid = 1 ; Will give you first day of last month. The way to use this is : create temp table t_date_test( id serial, ts date ) with no log; insert into t_date_test values (0,today - 1 ) ; insert into t_date_test values (0,today - 2 ) ; insert into t_date_test values (0,today - 3 ) ; insert into t_date_test values (0,today - 4 ) ; insert into t_date_test values (0,today - 5 ) ; insert into t_date_test values (0,today - 6 ) ; insert into t_date_test values (0,today - 7 ) ; insert into t_date_test values (0,today - 8 ) ; insert into t_date_test values (0,today - 9 ) ; insert into t_date_test values (0,today - 10 ) ; insert into t_date_test values (0,today - 11 ) ; insert into t_date_test values (0,today - 12 ) ; insert into t_date_test values (0,today - 13 ) ; insert into t_date_test values (0,today - 14 ) ; insert into t_date_test values (0,today - 15 ) ; insert into t_date_test values (0,today - 16 ) ; insert into t_date_test values (0,today - 17 ) ; insert into t_date_test values (0,today - 18 ) ; insert into t_date_test values (0,today - 19 ) ; insert into t_date_test values (0,today - 20 ) ; insert into t_date_test values (0,today + 1 ) ; insert into t_date_test values (0,today + 2 ) ; insert into t_date_test values (0,today + 3 ) ; insert into t_date_test values (0,today + 4 ) ; insert into t_date_test values (0,today + 5 ) ; insert into t_date_test values (0,today + 6 ) ; insert into t_date_test values (0,today + 7 ) ; insert into t_date_test values (0,today + 8 ) ; insert into t_date_test values (0,today + 9 ) ; insert into t_date_test values (0,today + 10 ) ; insert into t_date_test values (0,today + 11 ) ; insert into t_date_test values (0,today + 12 ) ; insert into t_date_test values (0,today + 13 ) ; insert into t_date_test values (0,today + 14 ) ; insert into t_date_test values (0,today + 15 ) ; insert into t_date_test values (0,today + 16 ) ; insert into t_date_test values (0,today + 17 ) ; insert into t_date_test values (0,today + 18 ) ; insert into t_date_test values (0,today + 19 ) ; insert into t_date_test values (0,today + 20 ) ; select * from t_date_test where ts >= (extend(current, year to month) -1 units month)::date and ts < extend(current, year to month)::date order by ts ; notice: the second case is less than because you are generating the first date of the current month. or if you like using between: select * from t_date_test where ts between (extend(current, year tomonth) - 1 units month)::date and extend(current, year to month)::date - 1 units day order by ts ; Note the very subtle difference of subtracting a day from the first day of the current month to get the last day of last month. Also notice in both queries that they can take advantage of a standard index if there is one available on ts because unlike the oracle query above because the field "ts" has no function applied to it. Each of the comparison values are constants for the query. You could probably do the same thing in Oracle and you should try. I have now gone over my 15 minute limit.

asmath wrote: > I have a date table with date column in the Informix Database. I need > to have the Previous Month condition. > > If I run the report on Jan 1st 2006 or Jan 2nd or any date in the month > of Jan 2006 I should get Dec 2005 1 to 31 data in the report. I f I run > the report on Feb 1st the report should show Jan Data. > > > > The below condition helps to get the data for the previous month in > Oracle Database. I need the same kind of code in Informix. > > > > TRUNC(TIME_TAB.DAY,'MON') = TRUNC(ADD_MONTHS(SYSDATE,-1),'MON') > > > > TIME_TAB is the Time Table and DAY is the date column with date > datatype. This is like Perl - TMTOWTDI (There's More Than One Way To Do It). Carsten's solution would work; it exploits the idiosyncrasies of IDS DATETIME types. A solution that exploits the idiosyncrasies of the DATE type (and the DATETIME YEAR TO DAY type) is: Time_tab.Day BETWEEN MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1 UNITS MONTH AND MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1 You could add a UNITS DAY on the last line, but that would make IDS work harder. The logic of the common expression produces the first day of the month for the 'reference date' (which is TODAY in this example). That date minus one month is the first day of the prior month; that date minus one is the last day of the prior month. This code is secure for leap years, etc. The 1 UNITS MONTH is unfortunate but simple; it is unfortunate because it converts the date to DATETIME, does the difference and then converts back to DATE. It is simpler than messing around wrapping years when the current (reference) month is January. You could stick with all DATE values by rephrasing the first line as: MDY(MONTH(MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1), 1, YEAR (MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1) ) Basically, the 'inner' MDY values are the last day of the prior month, and the 'outer' MDY calculates the first day of the month thus defined. It would be interesting to see which is quicker, but it is clear which is more compact. An invariant SPL procedure or two could be used to clarify things: Time_tab.Day BETWEEN first_day_of_month(first_day_of_month(TODAY)-1) AND last_day_of_month(first_day_of_month(TODAY)-1) Coding those functions is left as an exercise for the reader... -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Yes, Carsten's way will work and it is a very direct translation from the Oracle method, but I think your way or mine will use an index if needed. I don't think that Carsten's way will. I just put my money where my mouth was. I created an index on my previous example and did an explain on all of the queries: select * from t_date_test where ts >= (extend(current, year to month) -1 units month)::date and ts < extend(current, year to month)::date order by ts Estimated Cost: 1 Estimated # of Rows Returned: 4 1) informix.t_date_test: INDEX PATH (1) Index Keys: ts (Serial, fragments: ALL) (fragments might be eliminated at runtime because filter contains runtime constants) Lower Index Filter: informix.t_date_test.ts >= EXTEND (CURRENT year to fraction(3),year to month) - interval( 1) month(9) to month ::date Upper Index Filter: informix.t_date_test.ts < EXTEND (CURRENT year to fraction(3),year to month) ::date QUERY: ------ select * from t_date_test where ts between (extend(current, year tomonth) - 1 units month)::date and extend(current, year to month)::date - 1 units day order by ts Estimated Cost: 1 Estimated # of Rows Returned: 4 1) informix.t_date_test: INDEX PATH (1) Index Keys: ts (Serial, fragments: ALL) (fragments might be eliminated at runtime because filter contains runtime constants) Lower Index Filter: informix.t_date_test.ts >= EXTEND (CURRENT year to fraction(3),year to month) - interval( 1) month(9) to month ::date Upper Index Filter: informix.t_date_test.ts <= EXTEND (CURRENT year to fraction(3),year to month) ::date- interval( 1) day(9) to day QUERY: ------ select * from t_date_test where extend(ts, year to month) = extend(today, year to month) - (1 units month) Estimated Cost: 2 Estimated # of Rows Returned: 4 1) informix.t_date_test: SEQUENTIAL SCAN Filters: EXTEND (informix.t_date_test.ts ,year to month) = EXTEND (TODAY ,year to month) - interval( 1) month(9) to month I know for this example the cost isn't very different because I only have a few rows put if you have 10,000 rows you would notice a diffrence.

Oh yeah here are Jonathan's explains while I am at it. Notice that they also would clearly use an index: I just have one question: Why would adding units day make Informix work harder? I thought that was the default action when you wrote "- 1" with dates? So, I am curious about what is actually happening. select * from t_date_test where ts BETWEEN MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1 UNITS MONTH AND MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1 Estimated Cost: 1 Estimated # of Rows Returned: 4 1) informix.t_date_test: INDEX PATH (1) Index Keys: ts (Serial, fragments: ALL) (fragments might be eliminated at runtime because filter contains runtime constants) Lower Index Filter: informix.t_date_test.ts >= MDY (MONTH (TODAY ) , 1 , YEAR (TODAY ) ) - interval( 1) month(9) to month Upper Index Filter: informix.t_date_test.ts <= MDY (MONTH (TODAY ) , 1 , YEAR (TODAY ) ) - 1 QUERY: ------ select * from t_date_test where ts between MDY(MONTH(MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1), 1, YEAR (MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1) ) AND MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1 Estimated Cost: 1 Estimated # of Rows Returned: 4 1) informix.t_date_test: INDEX PATH (1) Index Keys: ts (Serial, fragments: ALL) (fragments might be eliminated at runtime because filter contains runtime constants) Lower Index Filter: informix.t_date_test.ts >= MDY (MONTH (MDY (MONTH (TODAY ) , 1 , YEAR (TODAY ) ) - 1 ) , 1 , YEAR (MDY (MONTH (TODAY ) , 1 , YEAR (TODAY ) ) - 1 ) ) Upper Index Filter: informix.t_date_test.ts <= MDY (MONTH (TODAY ) , 1 , YEAR (TODAY ) ) - 1