MySQL日期处理函数

MySQL日期处理函数_20160922

除了对文本字符串进行处理外，对日期处理是最常用的了，年、月、周、日等等，同时在日常工作报表中月报、周报、日报这样的报表也是最常见了。

 1 #二、mysql对日期的处理 
 2 SELECT #具体某一天
 3 DATE_FORMAT("2016-01-01","%d") AS 日1,
 4 DAY("2016-01-01") AS 日2,
 5 DATE_FORMAT("2016-01-01","%x%v")AS 年周,
 6 CURRENT_DATE AS 当天日期,
 7 LAST_DAY(CURRENT_DATE) AS 本月最后一天,
 8 DATE_ADD(LAST_DAY(CURRENT_DATE),INTERVAL 1 DAY) AS 下月第一天,
 9 DATE_ADD(DATE_ADD(LAST_DAY(CURRENT_DATE),INTERVAL 1 DAY),INTERVAL -1 MONTH) AS 本月第一天,
10 DATE_ADD(DATE_ADD(LAST_DAY(CURRENT_DATE),INTERVAL 1 DAY),INTERVAL -2 MONTH) AS 上月第一天,
11 DATE_ADD(CURRENT_DATE,INTERVAL -3 DAY) AS 往前3天,
12 DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY) AS 往前1天,
13 DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH) AS 上月今天,
14 TIMESTAMPDIFF(DAY,"2016-05-28","2016-06-01") AS 相差天数
15 
16 SELECT  # 某周
17 CURRENT_DATE AS 今日,CURDATE() AS 今日,
18 DATE_FORMAT(CURRENT_DATE,'%w') AS 本月第几周,
19 SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')) AS 上周日,
20 SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1) AS 本周周一,
21 DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AS 上周周一,# interval -12周为本周一往前推12周
22 DATE_FORMAT(CURRENT_DATE,"%x%v") AS 年周 ,DATE_ADD(CURRENT_DATE,INTERVAL -1 WEEK) AS 上周的今天,
23 
24 SELECT  # 具体某一月 
25 DATE_FORMAT(CURRENT_DATE,"%Y%m") AS 年月,
26 DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH) AS 上月今天,
27 DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH),"%Y%m") AS 上月,
28 PERIOD_DIFF(DATE_FORMAT("2016-06-01","%Y%m"),DATE_FORMAT("2016-05-28","%Y%m")) AS 相差月份_不考虑天数,  #仅比较月份
29 PERIOD_DIFF(DATE_FORMAT(CURRENT_DATE,"%Y%m"),DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH),"%Y%m")) AS 相差月份_不考虑天数,  #仅比较月份,
30 TIMESTAMPDIFF(MONTH,"2016-05-05","2016-06-06") AS 相差月数_考虑天数a,
31 TIMESTAMPDIFF(MONTH,"2016-05-05","2016-06-04") AS 相差月数_考虑天数b

日期处理函数

1、以年月为单位，计算每个城市每个月的金额

1 SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
2 FROM test_a03order AS a
3 GROUP BY city,DATE_FORMAT(order_time,"%Y%m")

年月为单位

2、以年周为单位计算每个城市每个周的金额

1 SELECT city AS 城市,DATE_FORMAT(order_time,"%x%v") AS 年周,SUM(pay_money) AS 金额
2 FROM test_a03order AS a
3 GROUP BY city,DATE_FORMAT(order_time,"%x%v")

年周为单位

3、以日为单位计算每个城市每日金额

1 SELECT city AS 城市,DATE(order_time) AS 订单日,SUM(pay_money) AS 金额
2 FROM test_a03order AS a
3 GROUP BY city,DATE(order_time)

以日为单位