From: | "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be> |
---|---|
To: | "Colin Wetherbee" <cww(at)denterprises(dot)org>, "Frank Bax" <fbax(at)sympatico(dot)ca> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: First day of month, last day of month |
Date: | 2008-04-24 14:42:26 |
Message-ID: | 4810B871.A3DD.0030.0@indicator.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)denterprises(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Nacef LABIDI | 2008-04-24 14:43:40 | Re: First day of month, last day of month |
Previous Message | Erik Jones | 2008-04-24 14:41:04 | Re: First day of month, last day of month |