除了对文本字符串进行处理外，对日期处理是最常用的了，年、月、周、日等等，同时在日常工作报表中月报、周报、日报这样的报表也是最常见了。
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 a3 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 a3 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 a3 GROUP BY city,DATE(order_time)
