[RESOLVED] select last week?

[tonyw]

hi,

Is there a 'proper' way to select items with a date belonging to the previous week?

I have this -

SELECT n from table where YEARweek(a datestamp) = YEARweek(CURRENT_DATE - INTERVAL 7 DAY) && year(datestamp)=year(current_date) ...

I think the flaw with that will come during the first week of January?

Is there a better way?

thanks
tony

[nemonoman]

Yearweek includes the year:
200731
200601

etc
SELECT n from table where YEARweek(a datestamp) = YEARweek(CURRENT_DATE - INTERVAL 7 DAY)

will suffice.

Yearweeks start at 00:00:00.000000001 midnight sunday to
11:59:59.99999999999 saturday

in 2006 there were 53 yearweeks, since 31 Dec was a Sunday
so yearweek 200653 ran from 31 Dec 2006 through 6 Jan 2007

Hope this helps

[tonyw]

Hey that's great news - clever old sql!

On a related subject, i note that php date() starts its concept of a week on a Monday 🙂

thanks for the answer 🙂

[nemonoman]

So it's a good idea to stice with one or other. I have found that the date functions of MySQL are so helpful and easy that I use them exclusively. Chaqu'un a son gout.

[tonyw]

nemonoman wrote:

So it's a good idea to stice with one or other. I have found that the date functions of MySQL are so helpful and easy that I use them exclusively. Chaqu'un a son gout.

Agreed.