MySQL function to get list of Mondays

Question

What is the most efficient way to get a list of all of the unique Mondays from a date field?

When I'm not all that concerned about efficiency, I have done something like:

DATE-weekday(DATE) + 1. But now I need to compute this on a large dataset and I don't want my user wishing for a Rubik's cube because it is taking so long. :)

Yes, the field is indexed.

EDIT:
What I need is a list of all of the weeks that contain records. I am creating a payroll report where the user will select the week to filter the report.

Here is what I came up with:

SELECT DISTINCT ((DATE(`timStart`)-DAYOFWEEK(`timStart`))+2)
FROM `time` 
ORDER BY 1 DESC

Anyone have any improvement to suggest?

Answer 1

"unique mondays from a date field" should be as simple as:

SELECT DISTINCT(`date`) FROM `table` WHERE WEEKDAY(`date`)=0

"weeks in which we have date values" should be as simple as:

SELECT DISTINCT(WEEK(`date`)) FROM `table` WHERE YEAR(`date`)=2010;

SELECT WEEK(now()),YEAR(now());

+-------------+-------------+
| WEEK(now()) | YEAR(now()) |
+-------------+-------------+
|          31 |        2010 | 
+-------------+-------------+

which will benefit you as well in your other payroll queries, using

WHERE WEEK(`date`)=31

Put your trust in mysql to handle things from there.