mysql 以周为单位记录数据_mysql数据库按周统计

mysql数据库按周统计

需求：统计本周的数据，上一周的数据，不管是不是跨年，星期一是第1天。

week函数总共分为8种情况：

以星期一还是星期天做为一周的第1天(一周7天从星期一到星期天)

一年的第1个星期，4天以上在本年还是星期的第1天在本年。

一年开始属于去年的天，返回0还是上一年的星期数。

This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used. See Section 5.1.8, “Server System Variables”.

The following table describes how the mode argument works.

Mode

First day of week

Range

Week 1 is the first week …

0

Sunday

0-53

with a Sunday in this year

1

Monday

0-53

with 4 or more days this year

2

Sunday

1-53

with a Sunday in this year

3

Monday

1-53

with 4 or more days this year

4

Sunday

0-53

with 4 or more days this year

5

Monday

0-53

with a Monday in this year

6

Sunday

1-53

with 4 or more days this year

7

Monday

1-53

with a Monday in this year

For mode values with a meaning of “with 4 or more days this year,” weeks are numbered according to ISO 8601:1988:

If the week containing January 1 has 4 or more days in the new year, it is week 1.

Otherwise, it is the last week of the previous year, and the next week is week 1.

mysql> SELECT WEEK('2008-02-20');

-> 7

mysql> SELECT WEEK('2008-02-20',0);

-> 7

mysql> SELECT WEEK('2008-02-20',1);

-> 8

mysql> SELECT WEEK('2008-12-31',1);

-> 53

If a date falls in the last week of the previous year, MySQL returns 0 if you do not use 2, 3, 6, or 7 as the optional mode argument:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

-> 2000, 0

One might argue that WEEK() should return 52 because the given date actually occurs in the 52nd week of 1999. WEEK() returns 0 instead so that the return value is “the week number in the given year.” This makes use of the WEEK() function reliable when combined with other functions that extract a date part from a date.

If you prefer a result evaluated with respect to the year that contains the first day of the week for the given date, use 0, 2, 5, or 7 as the optional mode argument.

mysql> SELECT WEEK('2000-01-01',2);

-> 52

Alternatively, use the YEARWEEK() function:

mysql> SELECT YEARWEEK('2000-01-01');

-> 199952

mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);

-> '52'