mysql时间函数时间差（mysql时间日期函数使用方法）

MYSQL五大类函数，前面已经介绍了聚合函数、字符串函数和数值型函数，还有时间日期函数和控制流函数，这篇介绍一下日期时间函数，日期和时间函数主要用于处理日期和时间，其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。

这一篇主要介绍常用的日期和时间函数，然后后面会给出常用的计算（本）月初、月末，本周、上周初末、昨日今日等时间的表达式。

函数名称	作用
CURDATE和CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME和CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW和SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定日期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
DAYOFYEAR	获取指定曰期是一年中的第几天
DAYOFMONTH	获取指定日期是一个月中是第几天
WEEK	获取指定日期是一年中的第几周
MONTH	获取指定日期中的月份
YEAR	获取年份
DATE_ADD和ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB和SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

【CURDATE和CURRENT_DATE】

CURDATE和CURRENT_DATE获取系统当前日期。可以通过"CURDATE()+0"将当前日期值转换为数值型。

SELECT CURDATE(),CURRENT_DATE(),CURDATE()+0

【CURTIME和CURRENT_TIME】

CURTIME和CURRENT_TIME获取系统当前时间。通过"CURRENT_TIME()+0"将当前时间值转换为数值型。

SELECT CURTIME(),CURRENT_TIME(),CURTIME()+0

【NOW和SYSDATE】

NOW和SYSDATE获取当前系统的日期和时间。NOW()函数始终获取的是SQL语句开始执行的时间，而 SYSDATE()函数则是动态获取的实时时间。

SELECT NOW(),SYSDATE()

【MONTHNAME】

使用MONTHNAME()函数返回指定日期中月份的名称。

SELECT
  birthday,
  MONTHNAME( birthday ) 
FROM
  wangfalin;

【DAYNAME】

使用DAYNAME()函数返回指定日期的工作日名称。

SELECT
  birthday,
  DAYNAME( birthday ) 
FROM
  wangfalin;

【DAYOFWEEK】

使用DAYOFWEEK()函数返回日期对应的周索引，星期天为开始1。

SELECT
  birthday,
  DAYOFWEEK(birthday) 
FROM
  wangfalin;

【DAYOFYEAR】

DAYOFYEAR()函数返回是一年中的第几天，范围为1～366。

SELECT
  birthday,
  DAYOFYEAR(birthday) 
FROM
  wangfalin;

【DAYOFMONTH】

DAYOFMONTH()函数返回是一个月中的第几天，范围为1～31。

SELECT
  birthday,
  DAYOFMONTH(birthday) 
FROM
  wangfalin;

【WEEK】

WEEK()函数计算日期 date 是一年中的第几周。WEEK(date,mode)函数允许指定星期是否起始于周日或周一，以及返回值的范围是否为 0～52 或 1～53。

• date是要获取周数的日期。
• mode是一个可选参数，用于确定周数计算的逻辑。

模式	一周的第一天	范围
0	星期日	0-53
1	星期一	0-53
2	星期日	1-53
3	星期一	1-53
4	星期日	0-53
5	星期一	0-53
6	星期日	1-53
7	星期一	1-53

SELECT
  birthday,
  WEEK(birthday,3) 
FROM
  wangfalin;

【MONTH】

MONTH(date)函数返回指定日期对应的月份，范围为1～12。

SELECT
  birthday,
  MONTH(birthday) 
FROM
  wangfalin;

【YEAR】

YEAR()函数从指定日期值中来获取年份值。

SELECT
  birthday,
  YEAR(birthday) 
FROM
  wangfalin;

【DATE_ADD和ADDDATE】

DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type) 两个函数的作用相同，都是用于执行日期的加运算。

• date是DATE或DATETIME的起始值
• INTERVAL expr type是要添加到起始日期值的间隔值

Type 值

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

SELECT DATE_ADD('2021-05-24 23:26:59',INTERVAL 1 SECOND) AS C1,
DATE_ADD('2021-05-24 23:26:59',INTERVAL '1:1' MINUTE_SECOND) AS C2,
ADDDATE('2021-05-24 23:26:59',INTERVAL -1 DAY) AS C3;

【DATE_SUB和SUBDATE】

DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)两个函数作用相同，都是执行日期的减法运算。

• date 是 DATE 或 DATETIME 的起始值
• expr 是一个字符串，用于确定从起始日期减去的间隔值。type 是 expr 可解析的间隔单位，例如DAY，HOUR等

SELECT DATE_SUB('2021-05-24 23:26:59',INTERVAL 1 SECOND) AS C1,
DATE_SUB('2021-05-24 23:26:59',INTERVAL '1:1' MINUTE_SECOND) AS C2,
SUBDATE('2021-05-24 23:26:59',INTERVAL -1 DAY) AS C3;

【ADDTIME、SUBTIME】

ADDTIME(time,expr)函数用于执行时间的加、减法运算。添加expr到time并返回结果。

SELECT ADDTIME('2021-05-24 21:26:59','1:2:3')

SELECT SUBTIME('2021-05-24 21:26:59','1:2:3')

【DATEDIFF】

DATEDIFF(date1，date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或date-time表达式。计算时只用到这些值的日期部分。

SELECT DATEDIFF('2021-11-30','2021-11-29') AS COL1,
DATEDIFF('2021-11-30','2021-12-15') AS col2;

【DATE_FORMAT】

DATE_FORMAT(date，format)函数是根据format指定的格式显示date值。

• date：是要格式化的有效日期值
• format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)

%a	工作日的缩写名称（Sun~Sat)
%b	月份的缩写名称（Jan…Dec)
%c	月份，数字形式（0~12)
%D	带有英语后缀的该月日期(0th, 2st, 3nd,…）
%d	该月日期，数字形式（00~31)
%e	该月日期，数字形式（(0~31)
%f	微秒（000000 …999999)
%H	以 2 位数表示 24 小时（00~23)
%h, %I	以 2 位数表示 12 小时（01~12)
%i	分钟，数字形式(00~59)
%j	—年中的天数（001~366)
%k	以 24 小时（0~23)表示
%l	以12小时（1~12)表示
%M	月份名称（January~December)
%m	月份，数字形式（00~12)
%p	上午（AM) 或下午（PM)
%r	时间，12小时制（小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM)
%S, %s	以 2 位数形式表示秒（00~59)
%T	时间，24 小时制（小时 (hh): 分钟 (mm): 秒数 (ss))
%U	周（00~53)，其中周日为每周的第一天
%u	周（00~53)，其中周一为每周的第一天
%V	周（01~53)，其中周日为每周的第一天，和％X同时使用
%v	周（01~53)，其中周一为每周的第一天，和%x同时使用
%W	星期标识（周日、周一、周二…周六）
%w	—周中的每日（0= 周日…6= 周六）
%X	该周的年份，其中周日为每周的第一天，数字形式，4 位数，和％V同时使用
%x	该周的年份，其中周一为每周的第一天，数字形式，4位数，和%v同时使用
%Y	4 位数形式表示年份
%y	2 位数形式表示年份
%%	%一个文字字符

SELECT
  birthday,
  DATE_FORMAT(birthday,'%Y%m %M')
FROM
  wangfalin;

【WEEKDAY】

WEEKDAY(d)返回d对应的工作日索引。0 表示周一，1 表示周二，……，6 表示周日。

SELECT
  birthday,
  WEEKDAY(birthday)
FROM
  wangfalin;

【常用日期表示方法】

【今天】

SELECT 
DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始',
DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';

【昨天】

SELECT 
DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始',
DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';

【上周】

SELECT 
DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一',
DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';

【本周】

SELECT 
DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一',
DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';

【上月】

SELECT 
DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初',
DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';

【本月】

SELECT 
DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初',
DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';

End