Select data for current week only

[BlackMage]

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 .

[Thomas Kellerer]

Post by BlackMage
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);

Post by BlackMage
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

[Sam Mason]

Post by Thomas Kellerer

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.

[Thomas Kellerer]

Post by Sam Mason
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

[Edwin Plauchu]

Postgresql has these functions
http://www.postgresql.org/docs/8.1/static/functions-formatting.html for
formatting data time

Post by BlackMage
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 .
--
http://www.nabble.com/Select-data-for-current-week-only-tp25177178p25177178.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-general

[Edwin Plauchu]

you need to group dates per number week

Post by Edwin Plauchu
Postgresql has these functions
http://www.postgresql.org/docs/8.1/static/functions-formatting.html for
formatting data time

Post by BlackMage
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 .
--
http://www.nabble.com/Select-data-for-current-week-only-tp25177178p25177178.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-general

[Gerhard Heift]

Post by BlackMage
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

[Sam Mason]

Post by BlackMage
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.

[Jasen Betts]

Post by BlackMage
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