| Data Bases > Pgsql Sql > Re: First day o... |
|
| << Topic |
< Post |
Post 1 of 1 Topic 3433 of 3559
|
Post > |
Topic >> |
Re: First day of month, last day of month
by Bart.Degryse@[EMAIL PROTECTED]
("Bart Degryse")
Apr 24, 2008 at 04:42 PM
|
--=__PartEEC7BB42.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
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@[EMAIL PROTECTED]
> 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=20
>>> current month.
>>
>> select * from mytable where extract(month from mydate) =3D
extract(month=
=20
>> from now()) and extract(year from mydate) =3D extract(year from now());
>=20
> Sorry; I was not thinking clearly - date_trunc is better for this:
>=20
> select * from mytable where date_trunc('month',mydate) =3D=20
> date_trunc('month',now());
I have some code that uses extract() for this sort of thing. Would you=20
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
--=20
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--=__PartEEC7BB42.1__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>Don't know whether it's better, but it's shorter.</DIV>
<DIV>With extract you have to make the extraction for both year and month
=
(unless cases where either one doesn't matter)</DIV>
<DIV>With date_trunc('month', ...) you throw away anything smaller =
than month in one step.</DIV>
<DIV>I suppose having to call the function date_trunc twice and extract 4
=
times in the given example could make</DIV>
<DIV>the date_trunc version slightly faster.</DIV>
<DIV>Just wondering how many times you would have to do it before noticing
=
the "speedup".<BR><BR><BR>>>> Colin Wetherbee
<cww@[EMAIL PROTECTED]
2008-04-24 16:15 >>><BR>Frank Bax wrote:<BR>> Frank
=
Bax wrote:<BR>>> Nacef LABIDI wrote:<BR>>>> is there a =
better method to retrieve all the rows with dates in the <BR>>>>
=
current month.<BR>>><BR>>> select * from mytable where =
extract(month from mydate) =3D extract(month <BR>>> from now()) and
=
extract(year from mydate) =3D extract(year from now());<BR>> <BR>> =
Sorry; I was not thinking clearly - date_trunc is better for this:<BR>>
=
<BR>> select * from mytable where date_trunc('month',mydate) =3D =
<BR>> date_trunc('month',now());<BR><BR>I have some code that uses =
extract() for this sort of thing. Would you <BR>mind explaining how
=
date_trunc() is better for this?<BR><BR>Most of my extract() results end =
up in <select> drop-down boxes in
HTML.<BR><BR>Thanks.<BR><BR>Colin<B=
R><BR><BR>-- <BR>Sent via pgsql-sql mailing list
(pgsql-sql@[EMAIL PROTECTED]
)=
<BR>To make changes to your subscription:<BR><A
href=3D"http://www.postgres=
ql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</A>=
<BR></DIV></BODY></HTML>
--=__PartEEC7BB42.1__=--
|
1 Posts in Topic:
|
Bart.Degryse@[EMAIL PROTE |
2008-04-24 16:42:26 |
|
Post A Reply:
|
