> I have a MySQL database table that has a date column , how can I
> query for all records in this week and group on day of week.
Define "this week" :-)
This week of this year:
SELECT datefld
FROM table
WHERE EXTRACT(WEEK FROM datefld) = EXTRACT(WEEK FROM CURRENT_DATE)
AND
EXTRACT(YEAR FROM datefld) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY EXTRACT(DAY FROM datefld)
This week of each year:
SELECT datefld
FROM table
WHERE EXTRACT(WEEK FROM datefld) = EXTRACT(WEEK FROM CURRENT_DATE)
GROUP BY EXTRACT(DAY FROM datefld)
The last 7 days:
SELECT datefld
FROM table
WHERE datefld BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 7 DAY
GROUP BY EXTRACT(DAY FROM datefld)
Be carefull though, a week doesn't start on the same day everywhere.
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]