MySQL常用的日期时间函数有哪些

在MySQL中，日期和时间是常见的数据类型，处理日期和时间数据是数据库操作中的重要部分。MySQL提供了丰富的日期和时间函数，用于处理、计算和格式化日期和时间数据。本文将介绍MySQL中常用的日期时间函数，帮助开发者更好地处理日期和时间相关的操作。

1. 获取当前日期和时间

1.1 NOW()

NOW() 函数返回当前的日期和时间，格式为 YYYY-MM-DD HH:MM:SS。

SELECT NOW();
-- 输出示例: 2023-10-05 14:30:45

1.2 CURDATE()

CURDATE() 函数返回当前日期，格式为 YYYY-MM-DD。

SELECT CURDATE();
-- 输出示例: 2023-10-05

1.3 CURTIME()

CURTIME() 函数返回当前时间，格式为 HH:MM:SS。

SELECT CURTIME();
-- 输出示例: 14:30:45

1.4 CURRENT_DATE 和 CURRENT_TIME

CURRENT_DATE 和 CURRENT_TIME 是 CURDATE() 和 CURTIME() 的别名，功能相同。

SELECT CURRENT_DATE, CURRENT_TIME;
-- 输出示例: 2023-10-05, 14:30:45

2. 提取日期和时间的部分

2.1 YEAR()

YEAR() 函数从日期或日期时间值中提取年份。

SELECT YEAR('2023-10-05');
-- 输出: 2023

2.2 MONTH()

MONTH() 函数从日期或日期时间值中提取月份。

SELECT MONTH('2023-10-05');
-- 输出: 10

2.3 DAY()

DAY() 函数从日期或日期时间值中提取日。

SELECT DAY('2023-10-05');
-- 输出: 5

2.4 HOUR()

HOUR() 函数从时间或日期时间值中提取小时。

SELECT HOUR('14:30:45');
-- 输出: 14

2.5 MINUTE()

MINUTE() 函数从时间或日期时间值中提取分钟。

SELECT MINUTE('14:30:45');
-- 输出: 30

2.6 SECOND()

SECOND() 函数从时间或日期时间值中提取秒。

SELECT SECOND('14:30:45');
-- 输出: 45

2.7 DAYOFWEEK()

DAYOFWEEK() 函数返回日期对应的星期几，1 表示星期日，2 表示星期一，依此类推。

SELECT DAYOFWEEK('2023-10-05');
-- 输出: 5 (星期四)

2.8 WEEKDAY()

WEEKDAY() 函数返回日期对应的星期几，0 表示星期一，1 表示星期二，依此类推。

SELECT WEEKDAY('2023-10-05');
-- 输出: 3 (星期四)

2.9 DAYOFYEAR()

DAYOFYEAR() 函数返回日期在一年中的第几天。

SELECT DAYOFYEAR('2023-10-05');
-- 输出: 278

3. 日期和时间的计算

3.1 DATE_ADD()

DATE_ADD() 函数用于在日期或日期时间值上添加指定的时间间隔。

SELECT DATE_ADD('2023-10-05', INTERVAL 1 DAY);
-- 输出: 2023-10-06

3.2 DATE_SUB()

DATE_SUB() 函数用于在日期或日期时间值上减去指定的时间间隔。

SELECT DATE_SUB('2023-10-05', INTERVAL 1 MONTH);
-- 输出: 2023-09-05

3.3 DATEDIFF()

DATEDIFF() 函数返回两个日期之间的天数差。

SELECT DATEDIFF('2023-10-05', '2023-10-01');
-- 输出: 4

3.4 TIMEDIFF()

TIMEDIFF() 函数返回两个时间或日期时间值之间的时间差。

SELECT TIMEDIFF('14:30:45', '12:00:00');
-- 输出: 02:30:45

3.5 TIMESTAMPDIFF()

TIMESTAMPDIFF() 函数返回两个日期或日期时间值之间的时间差，单位可以是年、月、日、小时、分钟或秒。

SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2023-10-05');
-- 输出: 9

4. 日期和时间的格式化

4.1 DATE_FORMAT()

DATE_FORMAT() 函数用于将日期或日期时间值格式化为指定的字符串格式。

SELECT DATE_FORMAT('2023-10-05 14:30:45', '%Y年%m月%d日 %H时%i分%s秒');
-- 输出: 2023年10月05日 14时30分45秒

4.2 STR_TO_DATE()

STR_TO_DATE() 函数用于将字符串转换为日期或日期时间值。

SELECT STR_TO_DATE('2023-10-05 14:30:45', '%Y-%m-%d %H:%i:%s');
-- 输出: 2023-10-05 14:30:45

5. 其他常用函数

5.1 UNIX_TIMESTAMP()

UNIX_TIMESTAMP() 函数返回当前日期时间或指定日期时间的UNIX时间戳（从1970年1月1日开始的秒数）。

SELECT UNIX_TIMESTAMP('2023-10-05 14:30:45');
-- 输出: 1696516245

5.2 FROM_UNIXTIME()

FROM_UNIXTIME() 函数将UNIX时间戳转换为日期时间格式。

SELECT FROM_UNIXTIME(1696516245);
-- 输出: 2023-10-05 14:30:45

5.3 LAST_DAY()

LAST_DAY() 函数返回指定日期所在月份的最后一天。

SELECT LAST_DAY('2023-10-05');
-- 输出: 2023-10-31

5.4 MAKEDATE()

MAKEDATE() 函数根据年份和一年中的第几天生成日期。

SELECT MAKEDATE(2023, 278);
-- 输出: 2023-10-05

5.5 MAKETIME()

MAKETIME() 函数根据小时、分钟和秒生成时间。

SELECT MAKETIME(14, 30, 45);
-- 输出: 14:30:45

6. 总结

MySQL提供了丰富的日期和时间函数，涵盖了从获取当前日期时间、提取日期时间部分、进行日期时间计算、格式化日期时间到处理UNIX时间戳等多种操作。熟练掌握这些函数，可以大大提高处理日期和时间数据的效率和准确性。在实际开发中，根据具体需求选择合适的函数，能够简化代码并提高数据库操作的性能。