Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 5419F2E007E
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Thu, 24 Apr 2008 11:42:48 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 47600-03
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Thu, 24 Apr 2008 11:42:38 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3])
	by developer.postgresql.org (Postfix) with ESMTP id CA2BD2E0065
	for <pgsql-sql@postgresql.org>; Thu, 24 Apr 2008 11:42:37 -0300 (ADT)
Received: from DOMEXT-MTA by WEBACCESS.INDICATOR.BEwith Novell_GroupWise;
	Thu, 24 Apr 2008 16:42:34 +0200
Message-Id: <4810B871.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 
Date: Thu, 24 Apr 2008 16:42:26 +0200
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: "Colin Wetherbee" <cww@denterprises.org>, "Frank Bax" <fbax@sympatico.ca>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: First day of month, last day of month
References: <f16f7ea00804240559y6694c19fx102e57ea4455b07d@mail.gmail.com><48
	1085D1.2010807@sympatico.ca>
	<48108813.6070401@sympatico.ca><481095FB.7090600@denterprises.org>
In-Reply-To: <481095FB.7090600@denterprises.org>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__PartEEC7BB42.1__="
X-imss-version: 2.050
X-imss-result: Passed
X-imss-approveListMatch: *@indicator.be
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200804/158
X-Sequence-Number: 30697

--=__PartEEC7BB42.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Don't know whether it's better, but it's shorter.
With extract you have to make the extraction for both year and month =
(unless cases where either one doesn't matter)
With date_trunc('month', ...) you throw away anything smaller than month =
in one step.
I suppose having to call the function date_trunc twice and extract 4 times =
in the given example could make
the date_trunc version slightly faster.
Just wondering how many times you would have to do it before noticing the =
"speedup".


>>> Colin Wetherbee <cww@denterprises.org> 2008-04-24 16:15 >>>
Frank Bax wrote:
> Frank Bax wrote:
>> Nacef LABIDI wrote:
>>> is there a better method to retrieve all the rows with dates in the=20
>>> current month.
>>
>> select * from mytable where extract(month from mydate) =3D extract(month=
=20
>> from now()) and extract(year from mydate) =3D extract(year from now());
>=20
> Sorry; I was not thinking clearly - date_trunc is better for this:
>=20
> select * from mytable where date_trunc('month',mydate) =3D=20
> date_trunc('month',now());

I have some code that uses extract() for this sort of thing.  Would you=20
mind explaining how date_trunc() is better for this?

Most of my extract() results end up in <select> drop-down boxes in HTML.

Thanks.

Colin


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

--=__PartEEC7BB42.1__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>Don't know whether it's better, but it's shorter.</DIV>
<DIV>With extract you have to make the extraction for both year and month =
(unless cases where either one doesn't matter)</DIV>
<DIV>With date_trunc('month', ...)&nbsp;you throw away anything smaller =
than month in one step.</DIV>
<DIV>I suppose having to call the function date_trunc twice and extract 4 =
times in the given example could make</DIV>
<DIV>the date_trunc version slightly faster.</DIV>
<DIV>Just wondering how many times you would have to do it before noticing =
the "speedup".<BR><BR><BR>&gt;&gt;&gt; Colin Wetherbee &lt;cww@denterprises=
.org&gt; 2008-04-24 16:15 &gt;&gt;&gt;<BR>Frank Bax wrote:<BR>&gt; Frank =
Bax wrote:<BR>&gt;&gt; Nacef LABIDI wrote:<BR>&gt;&gt;&gt; is there a =
better method to retrieve all the rows with dates in the <BR>&gt;&gt;&gt; =
current month.<BR>&gt;&gt;<BR>&gt;&gt; select * from mytable where =
extract(month from mydate) =3D extract(month <BR>&gt;&gt; from now()) and =
extract(year from mydate) =3D extract(year from now());<BR>&gt; <BR>&gt; =
Sorry; I was not thinking clearly - date_trunc is better for this:<BR>&gt; =
<BR>&gt; select * from mytable where date_trunc('month',mydate) =3D =
<BR>&gt; date_trunc('month',now());<BR><BR>I have some code that uses =
extract() for this sort of thing.&nbsp; Would you <BR>mind explaining how =
date_trunc() is better for this?<BR><BR>Most of my extract() results end =
up in &lt;select&gt; drop-down boxes in HTML.<BR><BR>Thanks.<BR><BR>Colin<B=
R><BR><BR>-- <BR>Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)=
<BR>To make changes to your subscription:<BR><A href=3D"http://www.postgres=
ql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</A>=
<BR></DIV></BODY></HTML>

--=__PartEEC7BB42.1__=--