Re: First day of month, last day of month

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=20
>>>> the current month.
>>>
>>> select * from mytable where extract(month from mydate) =3D=20=20
>>> extract(month from now()) and extract(year from mydate) =3D=20=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=20
>> date_trunc('month',now());
>
> I have some code that uses extract() for this sort of thing. 
Would=20=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=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=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=
=20
because I've been writing partitioning code a lot lately.

Erik Jones

DBA | Emma=AE
erik@[EMAIL PROTECTED]
 or 615.292.5888
615.292.0777 (fax)

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




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