Thread: First day of month, last day of month
Hi all,<br /><br />I want to select data from a table according to a date column in my table. I want to select all the rowswhich have date in the current month. So is there a way to get from the actual date the first day and the last day ofthe month or is there a better method to retrieve all the rows with dates in the current month.<br /><br />Thanks to all<br/>
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());
select * from mytable where date_trunc('month', mydate) = date_trunc('month', now());<br /><br /><br />>>>
FrankBax <fbax@sympatico.ca> 2008-04-24 15:06 >>><br />Nacef LABIDI wrote:<br />> is there a better
methodto retrieve all <br />> the rows with dates in the current month.<br /><br /><br />select * from mytable where
extract(monthfrom mydate) = extract(month <br />from now()) and extract(year from mydate) = extract(year from
now());<br/><br />-- <br />Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br />To make changes to your
subscription:<br/><a
href="http://www.postgresql.org/mailpref/pgsql">http://www.postgresql.org/mailpref/pgsql</a>-sql<br/>
am Thu, dem 24.04.2008, um 14:59:47 +0200 mailte Nacef LABIDI folgendes:
> Hi all,
>
> I want to select data from a table according to a date column in my table. I
> want to select all the rows which have date in the current month. So is there a
> way to get from the actual date the first day and the last day of the month or
test=*# select date_trunc('month', current_date), date_trunc('month',
current_date)+'1month'::interval-'1day'::interval; date_trunc | ?column?
------------------------+------------------------2008-04-01 00:00:00+02 | 2008-04-30 00:00:00+02
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
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());
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
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®
erik@myemma.com
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
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
>>> 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
Actually I want to select all rows whith dates between first day of the month 00:00:00 and last date of the month
23:59:59<br/><br /><div class="gmail_quote">On Thu, Apr 24, 2008 at 4:15 PM, Colin Wetherbee <<a
href="mailto:cww@denterprises.org">cww@denterprises.org</a>>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Frank Bax wrote:<br
/><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> Frank Bax
wrote:<br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div
class="Ih2E3d">Nacef LABIDI wrote:<br /><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204,
204);padding-left: 1ex;"> is there a better method to retrieve all the rows with dates in the current month.<br
/></blockquote><br/></div> select * from mytable where extract(month from mydate) = extract(month from now()) and
extract(yearfrom mydate) = extract(year from now());<br /></blockquote><br /> Sorry; I was not thinking clearly -
date_truncis better for this:<br /><br /> select * from mytable where date_trunc('month',mydate) =
date_trunc('month',now());<br/></blockquote><br /> I have some code that uses extract() for this sort of thing. Would
youmind 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<br /><font color="#888888"><br /><br /> --
<br/> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"
target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/>
Well, that's what it does afaikt.<br /><br />>>> "Nacef LABIDI" <nacef.l@gmail.com> 2008-04-24 16:43
>>><br/>Actually I want to select all rows whith dates between first day of the month 00:00:00 and last date
ofthe month 23:59:59<br /><br /><div class="gmail_quote">On Thu, Apr 24, 2008 at 4:15 PM, Colin Wetherbee <<a
href="mailto:cww@denterprises.org">cww@denterprises.org</a>>wrote:<br /><blockquote class="gmail_quote"
style="PADDING-LEFT:1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">Frank Bax wrote:<br
/><blockquoteclass="gmail_quote" style="PADDING-LEFT: 1ex; BORDER-LEFT: rgb(204,204,204) 1px solid">Frank Bax wrote:<br
/><blockquoteclass="gmail_quote" style="PADDING-LEFT: 1ex; BORDER-LEFT: rgb(204,204,204) 1px solid"><div
class="Ih2E3d">NacefLABIDI wrote:<br /><blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; BORDER-LEFT:
rgb(204,204,204)1px solid">is there a better method to retrieve all the rows with dates in the current month.<br
/></blockquote><br/></div>select * from mytable where extract(month from mydate) = extract(month from now()) and
extract(yearfrom mydate) = extract(year from now());<br /></blockquote><br />Sorry; I was not thinking clearly -
date_truncis better for this:<br /><br />select * from mytable where date_trunc('month',mydate) =
date_trunc('month',now());<br/></blockquote><br />I have some code that uses extract() for this sort of thing. Would
youmind 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<br /><font color="#888888"><br /><br />--
<br/>Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"
target="_blank">pgsql-sql@postgresql.org</a>)<br/>To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/>
<p>On Thursday 24 April 2008 10:47, Bart Degryse wrote:<p>> Well, that's what it does afaikt.<p><p>And what does <spanstyle="font-style:italic;text-decoration:underline;color:#000101">afaikt</span><span style="color:#000101"> mean?</span><p>--<p>Terry Lee Tucker<p>Turbo's IT Manager<p>Turbo, division of Ozburn-Hessey Logistics<p>2251 Jesse JewellPkwy NE<p>Gainesville, GA 30501<p>Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987<p>terry@turbocorp.com<p>www.turbocorp.com
On Thu, Apr 24, 2008 at 8:43 AM, Nacef LABIDI <nacef.l@gmail.com> wrote:
> Actually I want to select all rows whith dates between first day of the
> month 00:00:00 and last date of the month 23:59:59
Then you can just use date_trunc on the values in the database. Plus
if you're using timestamp WITHOUT timezone, you can index on it.
create index table_datefield_month_trunc on table
(date_trunc('month',datefield));
select * from table where
date_trunc('month',timestampfield)='2007-10-01 00:00:00';
On Thu, Apr 24, 2008 at 8:52 AM, Terry Lee Tucker <terry@chosen-ones.org> wrote: > > On Thursday 24 April 2008 10:47, Bart Degryse wrote: > > > Well, that's what it does afaikt. > > And what does afaikt mean? As Far As I Kan Tell? ??? I'm used to AFAIR, As Far As I Rekall... :)
sory, afaikt vas djust a tiping eror. mi apollogys<br /><br />>>> "Scott Marlowe" <scott.marlowe@gmail.com>2008-04-24 16:56 >>><br />On Thu, Apr 24, 2008 at 8:52 AM, Terry Lee Tucker <terry@chosen-ones.org>wrote:<br />> > On Thursday 24 April 2008 10:47, Bart Degryse wrote:<br />><br />>> Well, that's what it does afaikt.<br />><br />> And what does afaikt mean?<br /><br />As Far As I Kan Tell? ???<br /><br />I'm used to AFAIR, As Far As I Rekall... :)<br /><br />-- <br />Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br/>To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql">http://www.postgresql.org/mailpref/pgsql</a>-sql<br/>
> -----Mensaje original----- > De: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] En nombre de Scott Marlowe > > Then you can just use date_trunc on the values in the > database. Plus if you're using timestamp WITHOUT timezone, > you can index on it. > Did not understand this. Are you saying timestamps WITH timezone are NOT indexable or you mean that you cant build a partial index on a timestamp-with-time-zone returning function? Regards, Fernando.
On Apr 24, 2008, at 9:52 AM, Terry Lee Tucker wrote: > On Thursday 24 April 2008 10:47, Bart Degryse wrote: > > > Well, that's what it does afaikt. > afaikt -> as far as I can tell. Erik Jones DBA | Emma® erik@myemma.com 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
On Apr 24, 2008, at 9:56 AM, Scott Marlowe wrote: > On Thu, Apr 24, 2008 at 8:52 AM, Terry Lee Tucker <terry@chosen-ones.org > > wrote: >>> On Thursday 24 April 2008 10:47, Bart Degryse wrote: >> >>> Well, that's what it does afaikt. >> >> And what does afaikt mean? > > As Far As I Kan Tell? ??? > > I'm used to AFAIR, As Far As I Rekall... :) Or AFAICS, As Far As I Can See Erik Jones DBA | Emma® erik@myemma.com 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
On Thu, Apr 24, 2008 at 9:10 AM, Fernando Hevia <fhevia@ip-tel.com.ar> wrote: > > De: pgsql-sql-owner@postgresql.org > > [mailto:pgsql-sql-owner@postgresql.org] En nombre de Scott Marlowe > > > > > Then you can just use date_trunc on the values in the > > database. Plus if you're using timestamp WITHOUT timezone, > > you can index on it. > > > > Did not understand this. Are you saying timestamps WITH timezone are NOT > indexable or you mean that you cant build a partial index on a > timestamp-with-time-zone returning function? Correct, timestamptz or timestamp with timezone (timestamptz is the shorter alias) are not indexable because functions in an index must be immutable, and date_trunc on a timestamptz is not.
On Thu, Apr 24, 2008 at 9:16 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Apr 24, 2008 at 9:10 AM, Fernando Hevia <fhevia@ip-tel.com.ar> wrote:
> > > De: pgsql-sql-owner@postgresql.org
> > > [mailto:pgsql-sql-owner@postgresql.org] En nombre de Scott Marlowe
> >
> > >
> > > Then you can just use date_trunc on the values in the
> > > database. Plus if you're using timestamp WITHOUT timezone,
> > > you can index on it.
> > >
> >
> > Did not understand this. Are you saying timestamps WITH timezone are NOT
> > indexable or you mean that you cant build a partial index on a
> > timestamp-with-time-zone returning function?
>
> Correct, timestamptz or timestamp with timezone (timestamptz is the
> shorter alias) are not indexable because functions in an index must be
> immutable, and date_trunc on a timestamptz is not.
Note that if you are storing your time stamp as timestamptz, you can
use the "at time zone 'xyz'" construct to create an index, and as long
as you retrieve them with the same construct you'll get to use the
index.
create index test_ts_month_trunc on testtable (date_trunc('month',(ts
at time zone 'MST')));
select * from testtable where date_trunc('month',(ts at time zone
'MST'))='2007-10-01 00:00:00'::timestamp;
> -----Mensaje original-----
> De: Scott Marlowe [mailto:scott.marlowe@gmail.com]
>
>
> Note that if you are storing your time stamp as timestamptz,
> you can use the "at time zone 'xyz'" construct to create an
> index, and as long as you retrieve them with the same
> construct you'll get to use the index.
>
> create index test_ts_month_trunc on testtable
> (date_trunc('month',(ts at time zone 'MST')));
> select * from testtable
> where date_trunc('month',(ts at time zone 'MST'))='2007-10-01
00:00:00'::timestamp;
>
I see the point. Thanks for the elaboration.
It would be rather :
test=*# select date_trunc('month', current_date), date_trunc('month', current_date)+'1month':
"2008-04-01 00:00:00+02";"2008-04-30 23:59:59+02"
test=*# select date_trunc('month', current_date), date_trunc('month', current_date)+'1month':
:interval-'1sec'::interval;
"2008-04-01 00:00:00+02";"2008-04-30 23:59:59+02"
On Thu, Apr 24, 2008 at 3:15 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
am Thu, dem 24.04.2008, um 14:59:47 +0200 mailte Nacef LABIDI folgendes:> Hi all,test=*# select date_trunc('month', current_date), date_trunc('month', current_date)+'1month'::interval-'1day'::interval;
>
> I want to select data from a table according to a date column in my table. I
> want to select all the rows which have date in the current month. So is there a
> way to get from the actual date the first day and the last day of the month or
date_trunc | ?column?
------------------------+------------------------
2008-04-01 00:00:00+02 | 2008-04-30 00:00:00+02
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql