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 is there a better method to retrieve all the rows with dates in
the current month.

Thanks to all

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());

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

select * from mytable where date_trunc('month', mydate) = date_trunc('month', now());


>>> Frank Bax <fbax@sympatico.ca> 2008-04-24 15:06 >>>
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());

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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());

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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

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

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




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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';

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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...

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql