MySQL 日期函数

日期（date）相关
- DATE_FORMAT
- EXTRACT
日期加减
日期时间串拼接
日期时间类型格式化
间隔值 INTERVAL
转换格式
参考

日期（date）相关

CURDATE()、CURRENT_DATE()、CURRENT_DATE

同义，返回当前日期

select curdate();
select current_date;
select current_date();
-- 2020-06-04

`DATE_FORMAT`

DATE_FORMAT (datetime ,FormatCodes )

SELECT DATE_FORMAT(SYSDATE(), '%W')       星期,
       DATE_FORMAT(SYSDATE(), '%w')       星期,
       DATE_FORMAT(SYSDATE(), '%Y-%m-%d') 日期;

`EXTRACT`

EXTRACT(unit FROM date)：提取日期的指定部分

语法格式如下：

SELECT EXTRACT(unit FROM date)

select extract(second from sysdate()) 秒,
       extract(minute from sysdate()) 分,
       extract(hour from sysdate())   时,
       extract(day from sysdate())    日,
       extract(week from sysdate())   第几周,
       extract(month from sysdate())  月,
       extract(year from sysdate())   年;

DAY

语法格式如下：

DAY(date) //返回日期的天；DAY() 和DAYOFMONTH()的意义相同。
DAYNAME (date) //英文星期
DAYOFWEEK (date) //一周中的第几天,1为星期日，范围1-7
DAYOFMONTH(date)//一月中的第几天，一号为第一天,范围1-31
DAYOFYEAR (date) //一年中的第几天，范围1-366

示例如下：

select day(sysdate())        天,
       dayname(sysdate())    周几,
       dayofweek(sysdate())  第几天_周,
       dayofmonth(sysdate()) 第几天_月,
       dayofyear(sysdate())  第几天_年;

week

语法格式如下：

WEEKWEEK(date[,mode])

该函数返回date 对应的星期数。WEEK() 的双参数形式允许你指定该星期是否起始于周日或周一，以及返回值的范围是否为从0 到53 或从1 到53。若 mode参数被省略，则使用default_week_format系统自变量的值(不改变默认为0)。

WEEKDAY(date)
//返回date (0 = 周一, 1 = 周二, ... 6 = 周日)对应的工作日索引 


WEEKOFYEAR(date)
//将该日期的阳历周以数字形式返回，范围是从1到53。
//它是一个兼容度函数，相当于WEEK(date,3)。

month

语法格式如下：

MONTH(date)
//返回date 对应的月份，范围时从 1 到 12。

MONTHNAME(date)
//返回date 对应月份的全名。

select month(sysdate()),
       monthname(sysdate());
       
-- 6,June

year

语法格式如下：

YEAR(date) 

返回date 对应的年份,范围是从1000到9999。

YEARWEEK(date), YEARWEEK(date,start)

返回一个日期对应的年或周。start参数的工作同 start参数对 WEEK()的工作相同。结果中的年份可以和该年的第一周和最后一周对应的日期参数有所不同。

select year(sysdate()),
       yearweek(sysdate());

CURRENT_X

语法格式如下：

CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
NOW ( ) //当前时间

SELECT CURRENT_DATE(),
       CURRENT_TIME(),
       CURRENT_TIMESTAMP(),
       NOW();

日期加减

求差： `DATEDIFF`

date1-date2(只算日期，不算时间)

DATEDIFF (date1 ,date2 ) //两个日期差

SELECT DATEDIFF(SYSDATE(), '2017-08-04');
-- 1035

两个日期时间型相减: TIMEDIFF

TIMEDIFF(datetime1,datetime2)

SELECT TIMEDIFF(SYSDATE(), '2017-08-07 20:20:20'),
       TIMEDIFF('2017-08-07 20:20:20', SYSDATE());

`TIMESTAMPDIFF`

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

返回两个日期或日期时间类型参数 datetime_expr1 与 datetime_epr2 之间的整数差值。返回值所采用的单位由 unit 参数指定。unit 参数的取值范围是：FRAC_SECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或 YEAR。

-- 46、查询各学生的年龄（精确到月份）
-- 方法一
select s_id, s_name, floor(datediff(now(), s_birth) / 365) 年龄
from student;
-- 方法二
select s_id, s_name, timestampdiff(year, s_birth, now()) 年龄
from student;

`DATE_ADD`

DATE_ADD(date2 , INTERVAL d_value d_type )

在date2中加上日期或时间

SELECT DATE_ADD(SYSDATE(),INTERVAL 3 HOUR)

type:可为second,minute,hour,day,week,month and year;

`TIMESTAMPADD`

TIMESTAMPADD(unit,interval,datetime_expr)

函数将表示间隔值的整形参数 interval 添加到日期或日期时间参数 datetime_expr 上。interval 所采用的单位由 unit 参数指定。unit 参数的取值范围是：FRAC_SECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或 YEAR。

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+---------------------------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02')                     |
+---------------------------------------------------------+
| 2003-01-02 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

`DATE_SUB`

DATE_SUB (date2 , INTERVAL d_value d_type )

在date2上减去一个时间

参数同上

SELECT DATE_SUB(SYSDATE(),INTERVAL 3 hour);

根据日期判断秒，分，时，天，周，月，年

语法格式如下：

SECOND(datetime)//返回datetime的秒；
MINUTE(datetime)//返回datetime的分钟；
HOUR(datetime )//返回datetime的小时；
DAY (date) //返回日期的天；
WEEK (datetime [,start_of_week ]) //第几周
MONTH(datetime)//月份
YEAR(datetime)//年份
DATE (datetime ) //返回datetime的日期部分；

SELECT SECOND(SYSDATE()) 秒,
       MINUTE(SYSDATE()) 分,
       HOUR(SYSDATE())   时,
       DAY(SYSDATE())    日,
       WEEK(SYSDATE())   周,
       MONTH(SYSDATE())  月,
       YEAR(SYSDATE())   年,
       DATE(SYSDATE())   日期;

日期时间串拼接

语法格式如下：

MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串

SELECT MAKEDATE(2020, 55),
       MAKETIME(20, 34, 20);
       
-- 2020-02-24  20:34:20

日期时间类型格式化

DATE_FORMAT()函数

SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:%s') 日期时间,
       DATE_FORMAT(SYSDATE(), '%Y-%m-%d')          日期,
       DATE_FORMAT(SYSDATE(), '%H:%i:%s')          时间;

str_to_date()函数

字符串转换为date：

select str_to_date(
               '2016-12-15 16:48:40',
               '%Y-%m-%d %H:%i:%S') 日期时间,
       str_to_date(
               '2016-12-15 16:48:40',
               '%Y-%m-%d')          日期,
       str_to_date(
               '2016-12-15 16:48:40',
               '%H:%i:%S')          时间;

UNIX_TIMESTAMP() UNIX_TIMESTAMP(date)

如果调用不带参数，返回一个UNIX时间戳 (自“1970-01-0100:00:00”UTC) 的一个无符号整数。如果UNIX_TIMESTAMP() 被调用带有日期参数，它返回的参数为秒，自“1970-01-0100:00:00”UTC的值。日期可能是日期字符串，时间字符串，一个TIMESTAMP或数字格式 YYMMDD 或 YYYYMMDD。

日期转化为时间戳

SELECT UNIX_TIMESTAMP(),
       UNIX_TIMESTAMP(sysdate());
-- 1591264013,1591264013

时间戳转化为日期 from_unixtime

from_unixtime(unix_timestamp, format)

select from_unixtime(1584782175),
       from_unixtime(1584782175, '%Y-%m-%d');

-- 2020-03-21 17:16:15,2020-03-21

间隔值 `INTERVAL`

间隔值简介

MySQL间隔值主要用于日期和时间计算。要创建间隔值，请使用以下表达式：

INTERVAL expr unit 

后跟INTERVAL关键字是expr确定间隔值，并unit指定间隔单位。例如，要创建1天间隔，请使用以下表达式：

INTERVAL 1 DAY 

请注意，INTERVAL并且UNIT不区分大小写，因此以下表达式与上面的表达式相同：

interval 1 day 

主要使用日期和时间算术的间隔值，如下所示：

date + INTERVAL expr unit
date - INTERVAL expr unit 

间隔值也用于在各种时间的功能，例如DATE_ADD，DATE_SUB，TIMESTAMPADD和TIMESTAMPDIFF

间隔示例

以下语句2020-01-01为返回添加1天2020-01-02：

mysql> SELECT '2020-01-01' + INTERVAL 1 DAY;
+-------------------------------+
| '2020-01-01' + INTERVAL 1 DAY |
+-------------------------------+
| 2020-01-02                    |
+-------------------------------+
1 row in set (0.00 sec)

如果在包含a DATE或DATETIMEvalue 的表达式中使用了interval值，并且interval值位于表达式的右侧，则可以使用expr以下示例中所示的负值：

mysql> SELECT '2020-01-01' + INTERVAL -1 DAY;
+--------------------------------+
| '2020-01-01' + INTERVAL -1 DAY |
+--------------------------------+
| 2019-12-31                     |
+--------------------------------+
1 row in set (0.00 sec)

以下语句显示如何使用DATE_ADD和DATE_SUB从日期值中添加/减去1个月：

SELECT DATE_ADD('2020-01-01', INTERVAL 1 MONTH) 1_MONTH_LATER, 
       DATE_SUB('2020-01-01',INTERVAL 1 MONTH) 1_MONTH_BEFORE;
+---------------+----------------+
| 1_MONTH_LATER | 1_MONTH_BEFORE |
+---------------+----------------+
| 2020-02-01    | 2019-12-01     |
+---------------+----------------+
1 row in set (0.00 sec) 

以下查询使用TIMESTAMPADD(unit,interval,expression)函数将30分钟添加到时间戳值：

mysql> SELECT DATE_ADD('2020-01-01', INTERVAL 1 MONTH) 1_MONTH_LATER,
    -> DATE_SUB('2020-01-01',INTERVAL 1 MONTH) 1_MONTH_BEFORE;
+---------------+----------------+
| 1_MONTH_LATER | 1_MONTH_BEFORE |
+---------------+----------------+
| 2020-02-01    | 2019-12-01     |
+---------------+----------------+
1 row in set (0.00 sec)

间隔实际例

创建一个名为memberships演示的新表：

CREATE TABLE memberships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(355) NOT NULL,
    plan VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL
); 

memberships表中，expired_date列存储每个成员的成员资格的到期日期。

以下语句将一些行插入memberships表中。

INSERT INTO memberships(email, plan, expired_date)
VALUES('john.doe@example.com','Gold','2017-07-13'),
      ('jane.smith@example.com','Platinum','2017-07-10'),
      ('david.corp@example.com','Silver','2017-07-15'),
      ('julia.william@example.com','Gold','2017-07-20'),
      ('peter.drucker@example.com','Silver','2017-07-08'); 

假设今天是2017-07-06，您可以使用以下查询在7天内找到其成员资格已过期的成员：

select email,
       plan,
       expired_date,
       datediff(expired_date, '2017-07-06') remaining_days
from memberships
where '2017-07-06' between
          date_sub(expired_date, interval 7 day) and expired_date; 

在此查询中，我们使用DATE_SUB函数将间隔值（INTERVAL 7 DAY）指定的过期日期减去7天。

转换格式

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%I	小时 (01-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(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 位

参考

MySQL 日期与时间方面的函数

MySQL 日期函数

日期（date）相关

DATE_FORMAT

EXTRACT

日期加减

求差： DATEDIFF

TIMESTAMPDIFF

DATE_ADD

TIMESTAMPADD

DATE_SUB

日期时间串拼接

日期时间类型格式化

间隔值 INTERVAL

间隔值简介

间隔示例

间隔实际例

转换格式

参考

Comments

`DATE_FORMAT`

`EXTRACT`

求差： `DATEDIFF`

`TIMESTAMPDIFF`

`DATE_ADD`

`TIMESTAMPADD`

`DATE_SUB`

间隔值 `INTERVAL`