MySQL昨天，上周，上个月和去年的结果 [英] MySQL results yesterday, last week, last month and last year

问题描述

我的日期字段(artists.onlineDate)是yyy-mm-dd hh:mm:ss

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

现在我得到了:

-- 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

我试图解释.如果我们在星期三，并且我使用SELECT * FROM artists WHERE DATE(artists.onlineDate) = CURDATE()，那么我星期一到星期三.

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.

月份和年份相同.

我选择SUB_DATE是不正确的方法.

I thins SUB_DATE is not the right way.

有什么建议吗?

推荐答案

好，我找到了想要的东西:

OK I found what I was looking for at:

MySQL查询以选择上周的数据吗?

SELECT * FROM testwoche 
 WHERE WEEK (datum, 3) = WEEK(current_date, 3) - 1 
   AND YEAR(datum) = YEAR(current_date) ORDER BY datum DESC

同月

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

感谢大家的帮助！

这篇关于MySQL昨天，上周，上个月和去年的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！