first day of any week?

nemonoman

I want to show invoices by week:

SELECT *
FROM invoice
WHERE WEEK(timestamp_column)=$week

I want to let my users choose the week from a dropdown menu that says

Week of 1 June
Week of 8 June
Week of 15 June
.
.
.
etc.
Where 1 June=First day of week(22)
8 June=First day of week (23)
etc.

Can anyone figure out how to SELECT the first day of every week of a month (or quarter or year)?

My ever-so-clever kludge is to populate a table I call daterange with all the days of the year(s).

Then I

SELECT min(date_column)
FROM daterange
group by week(date_column)

But isn't there a way to get the same results without the kludgey daterange table?

barand

You could base it this

<?php

 $y = date('Y', strtotime('last sunday'));
 $m = date('n', strtotime('last sunday'));
 $d = date('j', strtotime('last sunday'));

 $weeks = 26;  // how many weeks back to go
 $sundays = array();
 for ($w = 0; $w < $weeks; $w++) $sundays[] = date('d M y', mktime(0,0,0,$m,$d - $w*7,$y));
 $sundays = array_reverse($sundays);
 echo "<pre>";
 print_r ($sundays);
 echo "</pre>";
?>

hth

nemonoman

Wow. Talk about an undocumented feature.

Here's the manual:

strtotime -- Parse about any english textual datetime description into a UNIX timestamp
Description

int strtotime (string time [, int now])

The function expects to be given a string containing an english date format and will try to parse that format into a UNIX timestamp.

Strtotime() example

1
2 echo strtotime ("10 march 2000") .
3

From this I would never have thought to try strtotime ('last sunday').

Thank for the tip.

barand

Heres an extract from my php manual - strtotime()

Because strtotime() behaves according to GNU date syntax, have a look at the GNU manual page titled Date Input Formats. Described there is valid syntax for the time parameter.

Example 1. strtotime() examples

echo strtotime ("now"), "\n";
echo strtotime ("10 September 2000"), "\n";
echo strtotime ("+1 day"), "\n";
echo strtotime ("+1 week"), "\n";
echo strtotime ("+1 week 2 days 4 hours 2 seconds"), "\n";
echo strtotime ("next Thursday"), "\n";
echo strtotime ("last Monday"), "\n";