Thread: Retrieve month from date

I am using PGSQL 8.2.3 on Windows XP.<br /><br />I have a table called "StudentFeesPayment" with columns "ReceiptNo"
and"ReceiptMonthYear".<br />The column, "ReceiptMonthYear" stores date in the format "yyyy-mm-dd". I have to find the
max(ReceiptNo)where Month of (ReceiptMonthYear)=4. Or whatever month I give. <br /><br />I tried:<br /><br />Select
max(ReceiptNo)from StudentFeesPayment where Extract(Month('ReceiptMonthYear'))=4;<br /><br />But it is not working. How
todo it?<br /><br /> 

am  Fri, dem 20.04.2007, um 17:49:33 +0530 mailte Rohit Khare folgendes:
> I am using PGSQL 8.2.3 on Windows XP.
> 
> I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
> "ReceiptMonthYear".
> The column, "ReceiptMonthYear" stores date in the format "yyyy-mm-dd". I have
> to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4. Or whatever
> month I give.
> 
> I tried:
> 
> Select max(ReceiptNo) from StudentFeesPayment where Extract(Month
> ('ReceiptMonthYear'))=4;
> 
> But it is not working. How to do it?

... where extract(month from "ReceiptMonthYear") = 4


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Thanks both of you,

I ran EXPLAIN command on above suggested query and got following result:

"Aggregate  (cost=2.77..2.79 rows=1 width=10)"
"  ->  Seq Scan on studentfeespayment  (cost=0.00..2.77 rows=1 width=10)"
"        Filter: (date_part('month'::text, (recieptmonthyear)::timestamp
without time zone) = 4::double precision)"

What does this mean? Can I optimize it better?

-- 
View this message in context: http://www.nabble.com/Retrieve-month-from-date-tf3617513.html#a10105471
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On 4/20/07, RPK <rpk.general@gmail.com> wrote:
>
> Thanks both of you,
>
> I ran EXPLAIN command on above suggested query and got following result:
>
> "Aggregate  (cost=2.77..2.79 rows=1 width=10)"
> "  ->  Seq Scan on studentfeespayment  (cost=0.00..2.77 rows=1 width=10)"
> "        Filter: (date_part('month'::text, (recieptmonthyear)::timestamp
> without time zone) = 4::double precision)"
>
> What does this mean? Can I optimize it better?

create index lala on "StudentFeesPayment"(Extract(Month from
"ReceiptMonthYear"))

explain Select max("ReceiptNo") from "StudentFeesPayment" where
Extract(Month from "ReceiptMonthYear")=4;

Aggregate  (cost=14.97..14.98 rows=1 width=4) ->  Bitmap Heap Scan on "StudentFeesPayment"  (cost=4.33..14.94
rows=10 width=4)       Recheck Cond: (date_part('month'::text,
("ReceiptMonthYear")::timestamp without time zone) = 4::double
precision)       ->  Bitmap Index Scan on lala  (cost=0.00..4.33 rows=10 width=0)             Index Cond:
(date_part('month'::text,
("ReceiptMonthYear")::timestamp without time zone) = 4::double
precision)

See:

http://www.postgresql.org/docs/8.2/static/indexes-expressional.html

What this query will return:

Select  Extract(Month from 4/20/2007) from dual;

I suspect "dual" is not for PGSQL but Oracle. But I need to run the above
query. What is the replacement of "dual" in PGSQL.


Bart Degryse wrote:
> 
> Please always include the error message you get when something isn't
> working.
>  
> If you defined your table with quoted identifiers (create table
> "StudentFeesPayment" as ...) then try
>   Select max("ReceiptNo") from "StudentFeesPayment" where Extract(Month
> from "ReceiptMonthYear")=4;
> else try
>   Select max(ReceiptNo) from StudentFeesPayment where Extract(Month
> from ReceiptMonthYear)=4;
> 
> 
> 
>>>> "Rohit Khare" <rpk.general@gmail.com> 2007-04-20 14:19 >>>
> I am using PGSQL 8.2.3 on Windows XP.
> 
> I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
> "ReceiptMonthYear".
> The column, "ReceiptMonthYear" stores date in the format "yyyy-mm-dd".
> I have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4.
> Or whatever month I give. 
> 
> I tried:
> 
> Select max(ReceiptNo) from StudentFeesPayment where
> Extract(Month('ReceiptMonthYear'))=4;
> 
> But it is not working. How to do it?
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Retrieve-month-from-date-tf3617513.html#a10106861
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

am  Fri, dem 20.04.2007, um 10:30:16 -0700 mailte RPK folgendes:
> 
> What this query will return:
> 
> Select  Extract(Month from 4/20/2007) from dual;
> 
> I suspect "dual" is not for PGSQL but Oracle. But I need to run the above
> query. What is the replacement of "dual" in PGSQL.

Simply "select extract(month from current_date);"

> 
> 
> Bart Degryse wrote:

please no silly fullquote below your text.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Fri, 2007-04-20 at 12:30, RPK wrote:
> What this query will return:
> 
> Select  Extract(Month from 4/20/2007) from dual;
> 
> I suspect "dual" is not for PGSQL but Oracle. But I need to run the above
> query. What is the replacement of "dual" in PGSQL.
> 

Well, you're going to have to create a special one row table with
contraints and triggers to always keep it at one row and now allow
anyone to insert into it and...

just kidding.  All you need is:

select extract(month from '4/20/2007'::date);

i.e. no need for the extraneous from dual there.