Postgres Pro
Downloads
Home   >   mailing lists

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

select with date_part and month failure....

From
"P.MO"
Date:

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.

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

From
"A. Kretschmer"
Date:
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....

From
Alban Hertroys
Date:
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 //