mysql 去年同星期_MySQL的昨天，上周，上个月和去年的结果

my datefield (artists.onlineDate) is yyy-mm-dd hh:mm:ss

right now I got:

-- Today

SELECT * FROM artists WHERE DATE(artists.onlineDate) = CURDATE()

-- Current Week

SELECT * FROM artists WHERE WEEK(artists.onlineDate, 3) = WEEK(CURDATE(), 3)

-- Current Month

SELECT * FROM artists WHERE MONTH(artists.onlineDate) = MONTH(CURDATE())

-- Current Year

SELECT * FROM artists WHERE YEAR(artists.onlineDate) = YEAR(CURDATE())

But what I need is exact: Yesterday, Last Week, Last Month, Last Year

I try to explain. if we got wednesday, and I use SELECT * FROM artists WHERE DATE(artists.onlineDate) = CURDATE(), then I get monday to wednesday.

I would like to have the monday to sunday of the last week. the calendar week before.

And the same for the month and year.

I thins SUB_DATE is not the right way.

Any suggestions?

解决方案

OK I found what I was looking for at:

SELECT * FROM testwoche

WHERE WEEK (datum, 3) = WEEK(current_date, 3) - 1

AND YEAR(datum) = YEAR(current_date) ORDER BY datum DESC

same for month

SELECT * FROM testwoche

WHERE month (datum) = month(current_date) - 1

AND YEAR(datum) = YEAR(current_date)

ORDER BY datum DESC

This gives back the last week from monday to sunday and the last month

Thanks everybody for helping!