pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRetrieve month from date
8+ messages / 6 participants
[nested] [flat]
* Retrieve month from date
@ 2007-04-20 12:19 Rohit Khare <rpk.general@gmail.com>
2007-04-20 12:29 ` Re: Retrieve month from date Bart Degryse <Bart.Degryse@indicator.be>
2007-04-20 12:35 ` Re: Retrieve month from date A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-20 16:13 ` Re: Retrieve month from date RPK <rpk.general@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Rohit Khare @ 2007-04-20 12:19 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Retrieve month from date
2007-04-20 12:19 Retrieve month from date Rohit Khare <rpk.general@gmail.com>
@ 2007-04-20 12:29 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-04-20 17:30 ` Re: Retrieve month from date RPK <rpk.general@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Bart Degryse @ 2007-04-20 12:29 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Retrieve month from date
2007-04-20 12:19 Retrieve month from date Rohit Khare <rpk.general@gmail.com>
2007-04-20 12:29 ` Re: Retrieve month from date Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-04-20 17:30 ` RPK <rpk.general@gmail.com>
2007-04-20 17:43 ` Re: Retrieve month from date A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-20 17:46 ` Re: Retrieve month from date Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 2 replies; 8+ messages in thread
From: RPK @ 2007-04-20 17:30 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Retrieve month from date
2007-04-20 12:19 Retrieve month from date Rohit Khare <rpk.general@gmail.com>
2007-04-20 12:29 ` Re: Retrieve month from date Bart Degryse <Bart.Degryse@indicator.be>
2007-04-20 17:30 ` Re: Retrieve month from date RPK <rpk.general@gmail.com>
@ 2007-04-20 17:43 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 0 replies; 8+ messages in thread
From: A. Kretschmer @ 2007-04-20 17:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Retrieve month from date
2007-04-20 12:19 Retrieve month from date Rohit Khare <rpk.general@gmail.com>
2007-04-20 12:29 ` Re: Retrieve month from date Bart Degryse <Bart.Degryse@indicator.be>
2007-04-20 17:30 ` Re: Retrieve month from date RPK <rpk.general@gmail.com>
@ 2007-04-20 17:46 ` Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2007-04-20 17:46 UTC (permalink / raw)
To: RPK <rpk.general@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Retrieve month from date
2007-04-20 12:19 Retrieve month from date Rohit Khare <rpk.general@gmail.com>
@ 2007-04-20 12:35 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 0 replies; 8+ messages in thread
From: A. Kretschmer @ 2007-04-20 12:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Retrieve month from date
2007-04-20 12:19 Retrieve month from date Rohit Khare <rpk.general@gmail.com>
@ 2007-04-20 16:13 ` RPK <rpk.general@gmail.com>
2007-04-20 16:33 ` Re: Retrieve month from date Rodrigo De León <rdeleonp@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: RPK @ 2007-04-20 16:13 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Retrieve month from date
2007-04-20 12:19 Retrieve month from date Rohit Khare <rpk.general@gmail.com>
2007-04-20 16:13 ` Re: Retrieve month from date RPK <rpk.general@gmail.com>
@ 2007-04-20 16:33 ` Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rodrigo De León @ 2007-04-20 16:33 UTC (permalink / raw)
To: pgsql-sql; +Cc: RPK <rpk.general@gmail.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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-04-20 17:46 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-20 12:19 Retrieve month from date Rohit Khare <rpk.general@gmail.com>
2007-04-20 12:29 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-04-20 17:30 ` RPK <rpk.general@gmail.com>
2007-04-20 17:43 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-20 17:46 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-20 12:35 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-04-20 16:13 ` RPK <rpk.general@gmail.com>
2007-04-20 16:33 ` Rodrigo De León <rdeleonp@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox