select with date_part and month failure....

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)

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

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@magproductions.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 //