From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | Colin Wetherbee <cww(at)denterprises(dot)org> |
Cc: | Frank Bax <fbax(at)sympatico(dot)ca>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: First day of month, last day of month |
Date: | 2008-04-24 14:41:04 |
Message-ID: | C573CE2F-5225-4B31-8657-601DE6D5E67D@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)myemma(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bart Degryse | 2008-04-24 14:42:26 | Re: First day of month, last day of month |
Previous Message | Colin Wetherbee | 2008-04-24 14:15:23 | Re: First day of month, last day of month |