From: Erik Jones <erik@myemma.com>
To: Colin Wetherbee <cww@denterprises.org>
Cc: Frank Bax <fbax@sympatico.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: First day of month, last day of month
Date: Thu, 24 Apr 2008 09:41:04 -0500
Message-ID: <C573CE2F-5225-4B31-8657-601DE6D5E67D@myemma.com> (raw)
In-Reply-To: <481095FB.7090600@denterprises.org>
References: <f16f7ea00804240559y6694c19fx102e57ea4455b07d@mail.gmail.com>
<481085D1.2010807@sympatico.ca>
<48108813.6070401@sympatico.ca>
<481095FB.7090600@denterprises.org>
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
>>>> the current month.
>>>
>>> select * from mytable where extract(month from mydate) =
>>> extract(month from now()) and extract(year from mydate) =
>>> extract(year from now());
>> Sorry; I was not thinking clearly - date_trunc is better for this:
>> select * from mytable where date_trunc('month',mydate) =
>> date_trunc('month',now());
>
> I have some code that uses extract() for this sort of thing. Would
> you mind explaining how date_trunc() is better for this?
>
> Most of my extract() results end up in <select> drop-down boxes in
> HTML.
extract will pull specific date unit value out of a given date/
timestamp/interval. date_trunc will "round" a given date/timestamp
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
because I've been writing partitioning code a lot lately.
Erik Jones
DBA | Emma®
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, cww@denterprises.org, fbax@sympatico.ca
Subject: Re: First day of month, last day of month
In-Reply-To: <C573CE2F-5225-4B31-8657-601DE6D5E67D@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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