MySQL获取时间、格式转换各类操作的方法是什么

在MySQL数据库中，时间操作是非常常见的需求。无论是获取当前时间、格式化时间、还是进行时间的加减运算，MySQL都提供了丰富的函数和方法来满足这些需求。本文将详细介绍MySQL中获取时间、格式转换以及各类时间操作的方法。

1. 获取当前时间

在MySQL中，获取当前时间有多种方法，常用的函数包括NOW()、CURDATE()、CURTIME()等。

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 SYSDATE() 函数

SYSDATE() 函数与 NOW() 类似，返回当前的日期和时间，但 SYSDATE() 返回的是执行到该函数时的时间，而 NOW() 返回的是语句开始执行时的时间。

SELECT SYSDATE();

输出示例：

2023-10-05 14:30:45

1.5 UTC_DATE()、UTC_TIME()、UTC_TIMESTAMP() 函数

这些函数返回当前的UTC日期、时间和时间戳。

SELECT UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP();

输出示例：

2023-10-05 | 14:30:45 | 2023-10-05 14:30:45

2. 时间格式化

在MySQL中，可以使用 DATE_FORMAT() 函数对日期和时间进行格式化。

2.1 DATE_FORMAT() 函数

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

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

输出示例：

2023-10-05 14:30:45

常用的格式化符号包括：

• %Y：四位数的年份
• %y：两位数的年份
• %m：月份（01-12）
• %d：日期（01-31）
• %H：小时（00-23）
• %i：分钟（00-59）
• %s：秒（00-59）
• %W：星期名称（Sunday-Saturday）
• %a：缩写的星期名称（Sun-Sat）
• %b：缩写的月份名称（Jan-Dec）
• %M：月份名称（January-December）

2.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

3. 时间加减运算

在MySQL中，可以使用 DATE_ADD() 和 DATE_SUB() 函数对日期和时间进行加减运算。

3.1 DATE_ADD() 函数

DATE_ADD() 函数用于对日期或时间进行加法运算。

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

输出示例：

2023-10-06 14:30:45

INTERVAL 后面可以跟以下单位：

• YEAR：年
• MONTH：月
• DAY：天
• HOUR：小时
• MINUTE：分钟
• SECOND：秒

3.2 DATE_SUB() 函数

DATE_SUB() 函数用于对日期或时间进行减法运算。

SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

输出示例：

2023-10-05 13:30:45

3.3 TIMESTAMPADD() 函数

TIMESTAMPADD() 函数用于对时间戳进行加法运算。

SELECT TIMESTAMPADD(HOUR, 1, NOW());

输出示例：

2023-10-05 15:30:45

3.4 TIMESTAMPDIFF() 函数

TIMESTAMPDIFF() 函数用于计算两个时间戳之间的差值。

SELECT TIMESTAMPDIFF(HOUR, '2023-10-05 14:30:45', '2023-10-05 16:30:45');

输出示例：

2

4. 时间提取

在MySQL中，可以使用 YEAR()、MONTH()、DAY() 等函数从日期或时间中提取特定的部分。

4.1 YEAR() 函数

YEAR() 函数用于提取日期或时间的年份部分。

SELECT YEAR(NOW());

输出示例：

2023

4.2 MONTH() 函数

MONTH() 函数用于提取日期或时间的月份部分。

SELECT MONTH(NOW());

输出示例：

10

4.3 DAY() 函数

DAY() 函数用于提取日期或时间的日期部分。

SELECT DAY(NOW());

输出示例：

5

4.4 HOUR() 函数

HOUR() 函数用于提取时间的小时部分。

SELECT HOUR(NOW());

输出示例：

14

4.5 MINUTE() 函数

MINUTE() 函数用于提取时间的分钟部分。

SELECT MINUTE(NOW());

输出示例：

30

4.6 SECOND() 函数

SECOND() 函数用于提取时间的秒部分。

SELECT SECOND(NOW());

输出示例：

45

4.7 DAYOFWEEK() 函数

DAYOFWEEK() 函数用于返回日期对应的星期几（1=Sunday, 2=Monday, …, 7=Saturday）。

SELECT DAYOFWEEK(NOW());

输出示例：

5

4.8 DAYOFYEAR() 函数

DAYOFYEAR() 函数用于返回日期在一年中的第几天（1-366）。

SELECT DAYOFYEAR(NOW());

输出示例：

278

4.9 WEEK() 函数

WEEK() 函数用于返回日期在一年中的第几周。

SELECT WEEK(NOW());

输出示例：

40

4.10 QUARTER() 函数

QUARTER() 函数用于返回日期所在的季度（1-4）。

SELECT QUARTER(NOW());

输出示例：

4

5. 时间比较

在MySQL中，可以使用 DATEDIFF()、TIMEDIFF() 等函数对日期和时间进行比较。

5.1 DATEDIFF() 函数

DATEDIFF() 函数用于计算两个日期之间的天数差。

SELECT DATEDIFF('2023-10-05', '2023-10-01');

输出示例：

4

5.2 TIMEDIFF() 函数

TIMEDIFF() 函数用于计算两个时间之间的差值。

SELECT TIMEDIFF('14:30:45', '12:30:45');

输出示例：

02:00:00

5.3 TIMESTAMPDIFF() 函数

TIMESTAMPDIFF() 函数用于计算两个时间戳之间的差值，可以指定单位。

SELECT TIMESTAMPDIFF(HOUR, '2023-10-05 12:30:45', '2023-10-05 14:30:45');

输出示例：

2

6. 时间戳操作

在MySQL中，时间戳（Timestamp）是一种特殊的数据类型，用于存储日期和时间。时间戳通常用于记录数据的创建或修改时间。

6.1 UNIX_TIMESTAMP() 函数

UNIX_TIMESTAMP() 函数用于将日期或时间转换为Unix时间戳（从1970年1月1日开始的秒数）。

SELECT UNIX_TIMESTAMP(NOW());

输出示例：

1696523445

6.2 FROM_UNIXTIME() 函数

FROM_UNIXTIME() 函数用于将Unix时间戳转换为日期或时间。

SELECT FROM_UNIXTIME(1696523445);

输出示例：

2023-10-05 14:30:45

6.3 TIMESTAMP() 函数

TIMESTAMP() 函数用于将日期或时间转换为时间戳。

SELECT TIMESTAMP('2023-10-05 14:30:45');

输出示例：

2023-10-05 14:30:45

7. 时间操作的综合应用

在实际应用中，时间操作往往是多种函数和方法的综合使用。以下是一些常见的综合应用场景。

7.1 计算两个日期之间的天数差

SELECT DATEDIFF('2023-10-05', '2023-10-01');

输出示例：

4

7.2 计算两个时间之间的分钟差

SELECT TIMESTAMPDIFF(MINUTE, '2023-10-05 12:30:45', '2023-10-05 14:30:45');

输出示例：

120

7.3 获取当前日期的前一天

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);

输出示例：

2023-10-04

7.4 获取当前日期的下个月

SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH);

输出示例：

2023-11-05

7.5 获取当前日期的季度

SELECT QUARTER(NOW());

输出示例：

4

7.6 获取当前日期的星期几

SELECT DAYOFWEEK(NOW());

输出示例：

5

7.7 获取当前日期的年份

SELECT YEAR(NOW());

输出示例：

2023

7.8 获取当前日期的小时数

SELECT HOUR(NOW());

输出示例：

14

7.9 获取当前日期的分钟数

SELECT MINUTE(NOW());

输出示例：

30

7.10 获取当前日期的秒数

SELECT SECOND(NOW());

输出示例：

45

8. 总结

MySQL提供了丰富的时间操作函数和方法，能够满足各种时间处理需求。无论是获取当前时间、格式化时间、进行时间的加减运算，还是提取时间的特定部分，MySQL都能轻松应对。掌握这些时间操作的方法，能够大大提高数据库操作的效率和灵活性。

在实际应用中，时间操作往往是多种函数和方法的综合使用。通过灵活运用这些函数和方法，可以实现复杂的时间处理逻辑，满足各种业务需求。希望本文能够帮助读者更好地理解和掌握MySQL中的时间操作。