Re: select with date_part and month failure....

A. Kretschmer wrote:
> am 18.04.2006, um 9:20:50 +0200 mailte P.MO folgendes:
>>Hello
>>
>>I have the same problem against various PostgreSQL 8.1.3, 8.0.2 and 7.4.7:
>>
>>I have a table containing periodical invoices. it contain's the last time the
>>invoce was printed and a period in month to be waited before next time.
>>My querry works with date_parts on days but never with months:
>>
>>create temp table tstdates (
>> lasttimedone date,
>> period integer
>>);
>>
>>insert into tstdates values ('2006-01-01',2);
>>insert into tstdates values ('2006-02-01',2);
>>insert into tstdates values ('2006-03-01',2);
>>insert into tstdates values ('2006-04-01',2);
>>
>>-- doesn' t works :
>>select * from tstdates where
>>date_part('month',now()-lasttimedone) >= period;
>
> now()-lasttimedone returns the number of days, not the months.
>
> You can use this:
>
> test=# select * from tstdates where date_part('month',age(lasttimedone)) >= period;
> lasttimedone | period
> --------------+--------
> 2006-01-01 | 2
> 2006-02-01 | 2
> (2 rows)

Or you could use intervals, something like:

select *
from tstdates
where lasttimedone <= now() - '1 month'::interval

In that case you may be better off using interval types for your period
field, with the added benefit that you can use 1 query for any type of
interval ('1 month', '15 days', '2 weeks', etc.). Your query would
become something like:

select * from tstdates where lasttimedone - period >= now()

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //