Don't know whether it's better, but it's shorter.
With extract you have to make the extraction for both year and month (unless cases where either one doesn't matter)
With date_trunc('month', ...) you throw away anything smaller than month in one step.
I suppose having to call the function date_trunc twice and extract 4 times in the given example could make
the date_trunc version slightly faster.
Just wondering how many times you would have to do it before noticing the "speedup".
>>> Colin Wetherbee <cww@denterprises.org> 2008-04-24 16:15 >>>
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.
Thanks.
Colin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql