有关星期的问题:
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)