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@xxxxxxxxxxxxxxxxx
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 //
