MySQL有关星期的问题

 

有关星期的问题：
 1. 某天是星期几：
      可以用MySQL内置的函数WEEKDAY、DAYOFWEEK、DAYNAME函数来获取星期几；
      WEEKDAY函数返回值为0-6，0代表Monday,1代表Tuesday,…6代表Sunday；
      DAYOFWEEK函数返回值为1-7，1代表Sunday，2代表Monday，…7代表Saturday；
      DAYNAME函数则返回日期具体的名称，它和参数lc_time_names有关，该参数控制返回的日期显示方式；

mysql> SELECT WEEKDAY('2014-01-14'),DAYOFWEEK('2014-01-14'),DAYNAME('2014-01-14'
);
+-----------------------+-------------------------+-----------------------+
| WEEKDAY('2014-01-14') | DAYOFWEEK('2014-01-14') | DAYNAME('2014-01-14') |
+-----------------------+-------------------------+-----------------------+
|                     1 |                       3 | Tuesday               |
+-----------------------+-------------------------+-----------------------+
1 row in set (0.00 sec)

 

mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Tuesday        |
+----------------+
1 row in set (0.00 sec)

mysql> SET lc_time_names='zh_CN';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| 星期二         |
+----------------+
1 row in set (0.07 sec)

     如果已知2011-01-01是周六，如果用户想知道某个日期是否是周六，可以通过下面的方式，查看余数是0：

mysql> SET @a='2011-01-01';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DATEDIFF(@a,'2011-01-08')%7;
+-----------------------------+
| DATEDIFF(@a,'2011-01-08')%7 |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(@a,'2013-01-06')%7;
+-----------------------------+
| DATEDIFF(@a,'2013-01-06')%7 |
+-----------------------------+
|                          -1 |
+-----------------------------+
1 row in set (0.00 sec)

 

  2. 按周统计：
     有时候我们需要按周统计一些数据，一般我们会用到WEEK()这个函数；
     WEEK(date[,mode])函数：返回date对应的星期数,若 mode参数被省略，则使用default_week_format系统自变量的值;

mysql> SELECT WEEK('1998-02-20');
+--------------------+
| WEEK('1998-02-20') |
+--------------------+
|                  7 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK('2011-01-01'),WEEK('2011-01-02');
+--------------------+--------------------+
| WEEK('2011-01-01') | WEEK('2011-01-02') |
+--------------------+--------------------+
|                  0 |                  1 |
+--------------------+--------------------+
1 row in set (0.00 sec)

可以看到，WEEK函数将2011-01-01（星期六）视为第一周，而将2011-01-02（星期日）视为第二周，因为WEEK函数是按照国外的习惯设计的，将周日视为每星期的开始；
另外，如果每周的报表要求按周二或周三开始，WEEK函数该如何解决呢；

例子：

mysql> CREATE TABLE sales(
    -> id INT AUTO_INCREMENT NOT NULL,
    -> date DATETIME NOT NULL,
    -> cost INT UNSIGNED NOT NULL,
    -> PRIMARY KEY(id));
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO sales(date,cost) VALUES('2010-12-31',100);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO sales(date,cost) VALUES('2011-01-01',200);
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO sales(date,cost) VALUES('2011-01-02',100);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO sales(date,cost) VALUES('2011-01-06',100);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO sales(date,cost) VALUES('2011-01-10',100);
Query OK, 1 row affected (0.00 sec)

查看分组情况：

mysql> SELECT WEEK(date),SUM(cost) FROM SALES
    -> GROUP BY WEEK(date);
+------------+-----------+
| WEEK(date) | SUM(cost) |
+------------+-----------+
|          0 |       200 |
|          1 |       200 |
|          2 |       100 |
|         52 |       100 |
+------------+-----------+
4 rows in set (0.07 sec)

可以看到，WEEK函数把2010-12-31作为第52周，但2010-12-31、2011-01-01、2011-01-02应该是同一周。可以通过参考的方法来解决；

mysql> SELECT FLOOR(DATEDIFF(date,'1900-01-01')/7) AS date1,
    -> SUM(cost) FROM sales
    -> GROUP BY FLOOR(DATEDIFF(date,'1900-01-01')/7);
+-------+-----------+
| date1 | SUM(cost) |
+-------+-----------+
|  5791 |       400 |
|  5792 |       100 |
|  5793 |       100 |
+-------+-----------+
3 rows in set (0.19 sec)

date1返回的是距离1900-01-01的周数，还可以：

mysql> SELECT DATE_ADD('1900-01-01',
    -> INTERVAL FLOOR(DATEDIFF(date,'1900-01-01')/7)*7 DAY)
    -> AS start,
    -> DATE_ADD('1900-01-01',
    -> INTERVAL FLOOR(DATEDIFF(date,'1900-01-01')/7)*7+6 DAY)
    -> AS end,
    -> SUM(cost) FROM sales
    -> GROUP BY FLOOR(DATEDIFF(date,'1900-01-01')/7);
+------------+------------+-----------+
| start      | end        | SUM(cost) |
+------------+------------+-----------+
| 2010-12-27 | 2011-01-02 |       400 |
| 2011-01-03 | 2011-01-09 |       100 |
| 2011-01-10 | 2011-01-16 |       100 |
+------------+------------+-----------+
3 rows in set (0.00 sec)

1900-01-01是星期一，如果要从星期二开始统计，直接将时间修改为1900-01-02即可；

 

   3. 计算工作日：

       建立存储过程来计算startTime和endTime之间的工作日数；

CREATE PROCEDURE pGetWorkDays(startTime DATETIME,endTime DATETIME)
BEGIN
SELECT FLOOR(days/7)*5+days%7
-CASE WHEN 6 BETWEEN wd AND wd+days%7-1 THEN 1 ELSE 0 END 
-CASE WHEN 7 BETWEEN wd AND wd+days%7-1 THEN 1 ELSE 0 END AS time
FROM
(SELECT DATEDIFF(endTime,startTime)+1 AS days,WEEKDAY(startTime)+1 AS wd) AS a;
END;

测试一下：

mysql> CALL pGetWorkDays('2013-01-01','2013-12-31');
+------+
| time |
+------+
|  261 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> CALL pGetWorkDays('2013-01-01','2013-12-30');
+------+
| time |
+------+
|  260 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)


mysql> CALL pGetWorkDays('2013-01-01','2013-12-29');
+------+
| time |
+------+
|  259 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> CALL pGetWorkDays('2013-01-01','2013-12-28');
+------+
| time |
+------+
|  259 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)