mysql 获取当前周，跨越去年和今年 [英] mysql get current week, spanning last year and this year

问题描述

我需要获取本周的所有记录.但由于 12 月 31 日星期一是 2012 年，mysql 将其视为第 53 周，而不是 2013 年的第 1 周.我该如何解决?

I need to fetch all records from current week. But since Monday Dec 31st was in 2012, mysql treats it as week 53, instead of week 1 of 2013. How can I get around that?

查询:

SELECT COUNT(*) AS `cnt`
FROM `orders`
WHERE WEEK(`date_purchased`, 1) = WEEK(NOW(), 1)
    AND (
           (
            MONTH(`date_purchased`) = MONTH(NOW() - INTERVAL 1 WEEK)
            AND YEAR(`date_purchased`) = YEAR(NOW() - INTERVAL 1 WEEK)
            )
        OR (
            MONTH(`date_purchased`) = MONTH(NOW())
            AND YEAR(`date_purchased`) = YEAR(NOW())
            )
        )

推荐答案

根据 ISO 标准，一年中的第 1 周是第一周，其大部分天数在起始年，而 week(date,1) 返回第 53 周的 '2012-12-31', week(date,3) 或其等效的 weekofyear(date) 正确返回周数 根据这个标准，所以:

According to ISO standard, week number 1 of the year is the first week with the majority of its days in the starting year, and while week(date,1) returns week 53 for '2012-12-31', week(date,3) or its equivalent weekofyear(date) correctly return week number according to this standard, so:

weekofyear('2013-01-01') = weekofyear('2012-12-31') = 1
-- week starts on Monday, and 2012-12-31 is part of the first week of 2013

weekofyear('2012-01-01') = weekofyear('2011-12-31') = 52
-- last week of 2011 starts on 12-26, first week of 2012 starts on 01-02

weekofyear('2010-01-01') = weekofyear('2009-12-31') = 53
-- some years have 53 weeks

weekofyear('2007-01-01') != weekofyear('2006-12-31')
-- that's correct because the first day of 2007 was a Monday

由于您的要求更具体，如果您必须检查两天是否属于同一年的同一周，这仍然不足以回答您的问题.

Since your request is more specific, if you have to check if two days are part of the same week of the same year, this is still not enough to answer your question.

与其使用 week 或 weekofyear，不如使用 yearweek(date,mode) 函数，使用 mode=3，返回与上面相同的周数，但在这周的年份之前是指:

Instead of using week, or weekofyear, it's better to use yearweek(date,mode) function, using mode=3, that returns the same week numbers as above, but also preceded by the year this week refers to:

yearweek('2013-01-01', 3) = yearweek('2012-12-31', 3) = 201301
yearweek('2012-01-01', 3) = yearweek('2011-12-31', 3) = 201152
yearweek('2010-01-01', 3) = yearweek('2009-12-31', 3) = 200952
yearweek('2007-01-01', 3) != yearweek('2006-12-31', 3)

所以你的查询可能变成:

so your query could become:

SELECT COUNT(*) AS `cnt`
FROM `orders`
WHERE YEARWEEK(`date_purchased`, 3) = YEARWEEK(DATE(), 3)

这篇关于mysql 获取当前周，跨越去年和今年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！