#二、mysql对日期的处理

 SELECT #具体某一天

 DATE_FORMAT("2016-01-01","%d") AS 日1,

 DAY("2016-01-01") AS 日2,

 DATE_FORMAT("2016-01-01","%x%v")AS 年周,

 CURRENT_DATE AS 当天日期,

 LAST_DAY(CURRENT_DATE) AS 本月最后一天,

 DATE_ADD(LAST_DAY(CURRENT_DATE),INTERVAL 1 DAY) AS 下月第一天,

 DATE_ADD(DATE_ADD(LAST_DAY(CURRENT_DATE),INTERVAL 1 DAY),INTERVAL -1 MONTH) AS 本月第一天,

 DATE_ADD(DATE_ADD(LAST_DAY(CURRENT_DATE),INTERVAL 1 DAY),INTERVAL -2 MONTH) AS 上月第一天,

 DATE_ADD(CURRENT_DATE,INTERVAL -3 DAY) AS 往前3天,

 DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY) AS 往前1天,

 DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH) AS 上月今天,

 TIMESTAMPDIFF(DAY,"2016-05-28","2016-06-01") AS 相差天数

 SELECT  # 某周

 CURRENT_DATE AS 今日,CURDATE() AS 今日,

 DATE_FORMAT(CURRENT_DATE,'%w') AS 本月第几周,

 SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')) AS 上周日,

 SUBDATE(CURRENT_DATE,DATE_FORMAT(CURRENT_DATE,'%w')-1) AS 本周周一,

 DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1),INTERVAL -1 WEEK) AS 上周周一,# interval -12周为本周一往前推12周

 DATE_FORMAT(CURRENT_DATE,"%x%v") AS 年周 ,DATE_ADD(CURRENT_DATE,INTERVAL -1 WEEK) AS 上周的今天,

 SELECT  # 具体某一月

 DATE_FORMAT(CURRENT_DATE,"%Y%m") AS 年月,

 DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH) AS 上月今天,

 DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH),"%Y%m") AS 上月,

 PERIOD_DIFF(DATE_FORMAT("2016-06-01","%Y%m"),DATE_FORMAT("2016-05-28","%Y%m")) AS 相差月份_不考虑天数,  #仅比较月份

 PERIOD_DIFF(DATE_FORMAT(CURRENT_DATE,"%Y%m"),DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL -1 MONTH),"%Y%m")) AS 相差月份_不考虑天数,  #仅比较月份,

 TIMESTAMPDIFF(MONTH,"2016-05-05","2016-06-06") AS 相差月数_考虑天数a,

 TIMESTAMPDIFF(MONTH,"2016-05-05","2016-06-04") AS 相差月数_考虑天数b

 SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额

 FROM test_a03order AS a

 GROUP BY city,DATE_FORMAT(order_time,"%Y%m")

 SELECT city AS 城市,DATE_FORMAT(order_time,"%x%v") AS 年周,SUM(pay_money) AS 金额

 FROM test_a03order AS a

 GROUP BY city,DATE_FORMAT(order_time,"%x%v") 

 SELECT city AS 城市,DATE(order_time) AS 订单日,SUM(pay_money) AS 金额

 FROM test_a03order AS a

 GROUP BY city,DATE(order_time)