Обсуждение: Select data for current week only

Hey all,

I've been trying to figure this problem out with just using sql but I'm not
sure how too. I have a table that holds events for each week but I only want
to select events happening for the current week(Mon-Sun). So can anyone help
me out with this just using sql? I've accomplished it sorta using php but
only within the current 7 day range(example Thursday-Thursday) but not by
the week.

The field I am using for sorting is a Date type with the format yyyy-mm-dd
hh:mm:ss .
--
View this message in context: http://www.nabble.com/Select-data-for-current-week-only-tp25177178p25177178.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

BlackMage wrote on 27.08.2009 20:09:
> I've been trying to figure this problem out with just using sql but I'm not
> sure how too. I have a table that holds events for each week but I only want
> to select events happening for the current week(Mon-Sun). So can anyone help
> me out with this just using sql? I've accomplished it sorta using php but
> only within the current 7 day range(example Thursday-Thursday) but not by
> the week.

SELECT *
FROM the_table
WHERE extract(week from the_date_column) = extract(date from current_date);

> The field I am using for sorting is a Date type with the format yyyy-mm-dd
> hh:mm:ss .

A date column does not have a format :)

Thomas

On Thu, Aug 27, 2009 at 11:09:36AM -0700, BlackMage wrote:
>
> Hey all,
>
> I've been trying to figure this problem out with just using sql but I'm not
> sure how too. I have a table that holds events for each week but I only want
> to select events happening for the current week(Mon-Sun). So can anyone help
> me out with this just using sql? I've accomplished it sorta using php but
> only within the current 7 day range(example Thursday-Thursday) but not by
> the week.
>
> The field I am using for sorting is a Date type with the format yyyy-mm-dd
> hh:mm:ss .

If you use a timestamp and an index on it, the best thing would be
something like this:

SELECT * FROM event where date_trunc('week', now()) <= event_date AND
event_date < date_trunc('week', now()) + '1 week'::interval

Regards,
  Gerhard

On Thu, Aug 27, 2009 at 08:36:45PM +0200, Thomas Kellerer wrote:
> BlackMage wrote on 27.08.2009 20:09:
> >I only want to select events happening for the current week(Mon-Sun).
>
> SELECT *
> FROM the_table
> WHERE extract(week from the_date_column) = extract(date from current_date);

The OP leaves it somewhat open, but wouldn't date_trunc be better here?
Something like:

  SELECT * FROM the_table
  WHERE date_trunc('week',the_date_column) = date_trunc('week',CURRENT_TIMESTAMP);

Otherwise you'll end up getting values for other years as well as the
current one.

--
  Sam  http://samason.me.uk/

On Thu, Aug 27, 2009 at 11:09:36AM -0700, BlackMage wrote:
> The field I am using for sorting is a Date type with the format yyyy-mm-dd
> hh:mm:ss .

Values that look like that are normally stored in timestamp columns, not
date column.  You wouldn't get the time part if it was just a date.

--
  Sam  http://samason.me.uk/

Sam Mason wrote on 27.08.2009 21:06:
>
> The OP leaves it somewhat open, but wouldn't date_trunc be better here?
>
> Otherwise you'll end up getting values for other years as well as the
> current one.

Good point, I didn't think of that :)

As an alternative, one could explicitely add the year into the where condition:

SELECT *
FROM the_table
WHERE extract(week from the_date_column) = extract(date from current_date)
AND extract(year from the_date_column) = extract(year from current_date);

but your solution is definitely more elegant ...

Thomas

On 2009-08-27, BlackMage <dsd7872@uncw.edu> wrote:
>
> Hey all,
>
> I've been trying to figure this problem out with just using sql but I'm not
> sure how too. I have a table that holds events for each week but I only want
> to select events happening for the current week(Mon-Sun). So can anyone help
> me out with this just using sql? I've accomplished it sorta using php but
> only within the current 7 day range(example Thursday-Thursday) but not by
> the week.
>
> The field I am using for sorting is a Date type with the format yyyy-mm-dd
> hh:mm:ss .

sounds like a timestamp. case it to date


 yourfield :: date
   between
     'today' :: date - extract (DOW from 'today' :: date )
   and
     'today' :: date - extract (DOW from 'today' :: date ) + 6