MySQL日期处理函数

最新推荐文章于 2024-10-10 21:45:00 发布

原创最新推荐文章于 2024-10-10 21:45:00 发布 · 353 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#大数据

服务器专栏收录该内容

4 篇文章

订阅专栏

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