Informix date functions help

[asmath]

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.

[bozon]

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 to
month) - 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.

[Carsten Haese]

extend(time_tab.day, year to month) = extend(today, year to month) - (1
units month)

should do the trick.

HTH,

Carsten.
sending to informix-list

[Jonathan Leffler]

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: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

[bozon]

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 to
month) - 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.

[bozon]

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

[Mark D. Stock]

bozon wrote:

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

Well a DATE value is stored as a simple INTEGER, and a DATETIME is stored
as an encoded DECIMAL. So a -1 is a simple operation on a DATE value.
However, as Jonathan said, "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." You can substitute DAY for
MONTH in that explanation. ;-)

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mds...@MydasSolutions.com |//////// /|
| |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.11/191 - Release Date: 02/12/2005
sending to informix-list

[bozon]

Thanks.