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® [EMAIL PROTECTED] 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
