MySQL周数和新年 [英] MySQL week numbers and New Year

问题描述

我正在使用的网站现在有一个仪表板，显示了各个用户在上周输入的数据.我使用简单的WHERE子句选择此数据:

The website I'm working on now has a dashboard showing data entered during the previous week by various users. I select this data using a simple WHERE clause:

SELECT * FROM table WHERE WEEK(date, 1) = WEEK(CURDATE(), 1) - 1

但是，新年快到了，当用户尝试在1月3日或1月4日查看仪表板时，我的代码显然会给我一个错误的结果，因为上周的数字2010年是52岁.因此，考虑到年份的变化，改变我的查询的最佳方法是什么.而且，很可能可以选择在2、3、4，...个星期前输入的数据.

However, the New Year is coming soon, and when a user tries to view the dashboard on, for example, 3rd or 4th of January, my code is clearly going to give me a wrong result, because the last week's number of 2010 is 52. So, what would be the best way to alter my query to take into account the change of the year. And also, it would be cool to make it possible to select data entered 2, 3, 4,... weeks ago.

推荐答案

如何选择7天前一天的WEEK?

How about selecting the WEEK of the day seven days ago?

WHERE WEEK(date, 1) = WEEK(CURDATE() - INTERVAL 1 WEEK, 1)

通过这种方式，您可以选择2、3、4周前输入的数据:

This way you can select data entered 2,3,4 weeks ago:

WHERE WEEK(date, 1) = WEEK(CURDATE() - INTERVAL 2 WEEK, 1)

这篇关于MySQL周数和新年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！