MYSQL 常用日期函数

1.日期

1.1获取日期

#当前日期 + 时间： 2024-04-02 16:51:40

SELECT NOW();

#当前日期： 2024-04-02

SELECT CURRENT_DATE();

SELECT CURDATE();

#当前时间： 16:51:40

SELECT CURRENT_TIME();

SELECT CURTIME();

除了now()以外, 以下同样可获取日期+时间, 鉴于now()简短易记, 推荐使用now()

SELECT CURRENT_TIMESTAMP();

    --> 2024-04-03 09:58:31

SELECT CURRENT_TIMESTAMP;

    --> 2024-04-03 09:58:31



SELECT LOCALTIME();

    --> 2024-04-03 09:58:31

SELECT LOCALTIME;

    --> 2024-04-03 09:58:31



SELECT LOCALTIMESTAMP();

    --> 2024-04-03 09:58:31

SELECT LOCALTIMESTAMP;

    --> 2024-04-03 09:58:31

1.2 获取年月日

#从当前时间, 分别获取其年、月、日、时、分、秒

#获取日期

SELECT DATE(NOW());

        --> 2024-04-02

#获取时间

SELECT TIME(NOW());

        --> 17:08:26

#获取年: 2024

SELECT YEAR(NOW());

SELECT EXTRACT(YEAR from NOW());

#获取月: 4

SELECT MONTH(NOW());

SELECT EXTRACT(MONTH from NOW());

#获取日: 2

SELECT DAY(NOW());

SELECT EXTRACT(DAY from NOW());

#获取时: 17

SELECT HOUR(NOW());

SELECT EXTRACT(HOUR  from NOW());

#获取分: 8

SELECT MINUTE(NOW());

SELECT EXTRACT(MINUTE from NOW());

#获取秒: 26

SELECT SECOND(NOW());

SELECT EXTRACT(SECOND from NOW());

#从日期格式字符串获取: 26

SELECT SECOND("2024-04-02 17:08:26");

2.时间戳

2.1 获取

#获取当前时间戳(秒)

SELECT UNIX_TIMESTAMP();

SELECT UNIX_TIMESTAMP(NOW());

#日期格式转时间戳(秒)

SELECT UNIX_TIMESTAMP("2024-04-02");

SELECT UNIX_TIMESTAMP("2024-04-02 17:23:38");

2.2 时间戳转日期

#时间戳转日期

SELECT FROM_UNIXTIME(1712049818);

        --> 2024-04-02 17:23:38

#指定格式

SELECT FROM_UNIXTIME(1712049818,"%Y-%m-%d");

        --> 2024-04-02

SELECT FROM_UNIXTIME(1712049818,"%H:%i:%s");

        --> 17:23:38

SELECT FROM_UNIXTIME(1712049818,"%Y-%m-%d %H:%i:%s");

        --> 2024-04-02 17:23:38

SELECT FROM_UNIXTIME(1712049818,"%Y年%m月%d日 %H:%i:%s");

        --> 2024年04月02日 17:23:38

3. 加减

3.1 日期加减

3.1.1 加

ADDDATE(expr, days) #加上指定天数

ADDDATE(date, INTERVAL expr unit)

DATE_ADD(date, INTERVAL expr unit)

#当前时间加2天

SELECT ADDDATE(NOW(), 2);

    --> 2024-04-04 17:41:29

SELECT ADDDATE(NOW(),INTERVAL 2 DAY);

    --> 2024-04-04 17:41:29

SELECT DATE_ADD(NOW(),INTERVAL 2 DAY);

    --> 2024-04-04 17:41:29

3.1.2 减

SUBDATE(expr, days)  #减去指定天数

SUBDATE(date, INTERVAL expr unit)

DATE_SUB(date, INTERVAL expr unit)

#当前时间减2天

SELECT SUBDATE(NOW(), 2);

    --> 2024-04-04 17:41:29

SELECT SUBDATE(NOW(),INTERVAL 2 DAY);

    --> 2024-03-31 17:41:29

SELECT DATE_SUB(NOW(),INTERVAL 2 DAY);

    --> 2024-03-31 17:41:29

#其他间隔

YEAR、MONTH、DAY、HOUR、MINUTE、SECOND

更多参考:

https://dev.mysql.com/doc/refman/5.7/en/expressions.html#temporal-intervals

3.2 时间加减

ADDTIME(expr1, expr2)

SUBTIME(expr1, expr2)

expr1, expr2 需要在相同的format下

SELECT ADDTIME("2024-04-02 17:56:55", "1 1:1:1");

    --> 2024-04-04 18:57:56



SELECT SUBTIME("2024-04-02 17:56:55", "1 1:1:1");

    --> 2024-03-31 16:55:54

4.时间差

1.DATEDIFF

DATEDIFF(expr1, expr2)

# 天数差: expr1 - expr2

SELECT DATEDIFF("2024-04-02", "2024-04-04");

--> -2

SELECT DATEDIFF("2024-04-02 17:58:34", "2024-03-02 18:30:00");

--> 31

2.TIMEDIFF

TIMEDIFF(expr1, expr2)

# 时间差: expr1 − expr2

SELECT TIMEDIFF("2024-04-02 17:58:34", "2024-04-02 14:58:34");

--> 03:00:00

3.TIMESTAMPDIFF

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

#返回 datetime_expr2 − datetime_expr1 

#要求datetime_expr2、datetime_expr1是date或datetime格式，能被TIMESTAMP()解出

# 月份

SELECT TIMESTAMPDIFF(MONTH, "2024-05-10", "2024-06-10");

-- > 1

SELECT TIMESTAMPDIFF(MONTH, "2024-05-10", "2024-06-09");

--> 0

# 年        

SELECT TIMESTAMPDIFF(YEAR, "2024-05-01", "2020-01-01");

--> -4

# 分

SELECT TIMESTAMPDIFF(MINUTE, "2024-05-01", "2024-05-01 00:05:55");

--> 5

# 天

SELECT TIMESTAMPDIFF(DAY, "2024-05-01", "2024-05-10");

--> 9

SELECT TIMESTAMPDIFF(DAY, "2024-05-01", "2024-05-10 12:05:55");

--> 9

5.附录

更多日期和时间参数, 参考:

MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions