Received: from localhost (unknown [200.46.204.184])
	by developer.postgresql.org (Postfix) with ESMTP id 902052E006A
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Thu, 24 Apr 2008 11:42:40 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 67643-06
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Thu, 24 Apr 2008 11:42:23 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from smtp136.iad.emailsrvr.com (smtp136.iad.emailsrvr.com
	[207.97.245.136])
	by developer.postgresql.org (Postfix) with ESMTP id F084D2E0054
	for <pgsql-sql@postgresql.org>; Thu, 24 Apr 2008 11:42:32 -0300 (ADT)
Received: from relay3.r3.iad.emailsrvr.com (localhost [127.0.0.1])
	by relay3.r3.iad.emailsrvr.com (SMTP Server) with ESMTP id 09B9344C544;
	Thu, 24 Apr 2008 10:42:31 -0400 (EDT)
Received: by relay3.r3.iad.emailsrvr.com (Authenticated sender:
	erik-AT-myemma.com) with ESMTP id 5F64A44C580;
	Thu, 24 Apr 2008 10:41:07 -0400 (EDT)
Cc: Frank Bax <fbax@sympatico.ca>,
 pgsql-sql@postgresql.org
Message-Id: <C573CE2F-5225-4B31-8657-601DE6D5E67D@myemma.com>
From: Erik Jones <erik@myemma.com>
To: Colin Wetherbee <cww@denterprises.org>
In-Reply-To: <481095FB.7090600@denterprises.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Apple Message framework v919.2)
Subject: Re: First day of month, last day of month
Date: Thu, 24 Apr 2008 09:41:04 -0500
References: <f16f7ea00804240559y6694c19fx102e57ea4455b07d@mail.gmail.com>
	<481085D1.2010807@sympatico.ca> <48108813.6070401@sympatico.ca>
	<481095FB.7090600@denterprises.org>
X-Mailer: Apple Mail (2.919.2)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200804/157
X-Sequence-Number: 30696


On Apr 24, 2008, at 9:15 AM, Colin Wetherbee wrote:

> Frank Bax wrote:
>> Frank Bax wrote:
>>> Nacef LABIDI wrote:
>>>> is there a better method to retrieve all the rows with dates in =20
>>>> the current month.
>>>
>>> select * from mytable where extract(month from mydate) =3D =20
>>> extract(month from now()) and extract(year from mydate) =3D =20
>>> extract(year from now());
>> Sorry; I was not thinking clearly - date_trunc is better for this:
>> 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 =20=

> you mind explaining how date_trunc() is better for this?
>
> Most of my extract() results end up in <select> drop-down boxes in =20
> HTML.


extract will pull specific date unit value out of a given date/=20
timestamp/interval.  date_trunc will "round" a given date/timestamp =20
down to the given unit.

extract(month from now()) -> 4

date_trunc('month', now()) -> 2008-04-01 00:00:00-05

I typically find date_trunc much more useful but I may just think that =20=

because I've been writing partitioning code a lot lately.

Erik Jones

DBA | Emma=AE
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com