Re: select with date_part and month failure....
Re: select with date_part and month failure....
От:
"A. Kretschmer" <andreas.kretschmer@schollglas.com>
Дата:
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 ===
Re: select with date_part and month failure....
От:
Alban Hertroys <alban@magproductions.nl>
Дата:
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 //
select with date_part and month failure....
От:
"P.MO" <04mic4ht65k1nv3@jetable.net>
Дата:
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;
-- works :
select * from tstdates where
date_part('days',now()-lasttimedone) >= period*30;
Can someone point me an error ?
T.I.A. PM.
