MySQL - 日期和时间函数

MySQL：日期和时间函数实用指南

MySQL 提供了一套丰富的函数来处理 DATE（日期）、TIME（时间）、DATETIME（日期时间）和 TIMESTAMP（时间戳）数据。掌握这些函数对于从日志事件和任务调度到时间序列数据分析的所有方面都至关重要。本指南将最常用的函数组织成实用类别，并提供现代示例。

存储日期和时间的最佳实践

使用正确的数据类型： 仅存储日期时选择 DATE，仅存储时间时选择 TIME，日期和时间的组合选择 DATETIME，以及需要感知时区的时间点选择 TIMESTAMP（它以 UTC 存储值，并在检索时将其转换回会话的时区）。
以 UTC 存储： 对于拥有多个时区用户的应用程序，将所有 TIMESTAMP 和 DATETIME 值存储在协调世界时（UTC）中是一项关键的最佳实践。在你的应用程序层处理向用户本地时区的转换。
使用 DEFAULT CURRENT_TIMESTAMP： 对于 created_at 或 updated_at 等列，使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 让 MySQL 自动管理它们。

类别 1：获取当前日期和时间

这些函数从运行 MySQL 的服务器中检索当前日期和时间。

函数	描述
`NOW()`、`CURRENT_TIMESTAMP()`	返回当前日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’。
`CURDATE()`、`CURRENT_DATE()`	返回当前日期，格式为 ‘YYYY-MM-DD’。
`CURTIME()`、`CURRENT_TIME()`	返回当前时间，格式为 ‘HH:MM:SS’。
`UTC_TIMESTAMP()`、`UTC_DATE()`、`UTC_TIME()`	返回当前的协调世界时（UTC）日期和/或时间。

示例

SELECT NOW(), CURDATE(), UTC_TIMESTAMP();

类别 2：提取日期或时间的部分

这些函数允许你从日期/时间值中提取特定组件，这对于数据分组和过滤非常有用。

函数	描述
`YEAR(date)`、`MONTH(date)`、`DAY(date)`	提取年份、月份或月份中的日期。
`HOUR(time)`、`MINUTE(time)`、`SECOND(time)`	提取小时、分钟或秒。
`DAYNAME(date)`	返回工作日的完整名称（例如，‘Sunday’）。
`MONTHNAME(date)`	返回月份的完整名称（例如，‘January’）。
`QUARTER(date)`	返回一年中的季度（1-4）。
`WEEKOFYEAR(date)`	返回日期在一年中的日历周（1-53）。
`EXTRACT(unit FROM date)`	一个多功能函数，用于提取特定单位（例如，`YEAR`、`MONTH`、`DAY`、`HOUR_MINUTE`）。

示例：分析用户注册

假设有一个包含 created_at 时间戳的 users 表。我们可以分析注册趋势。

SELECT
    COUNT(id) AS signup_count,
    YEAR(created_at) AS signup_year,
    MONTHNAME(created_at) AS signup_month
FROM users
WHERE YEAR(created_at) = 2023
GROUP BY signup_year, signup_month
ORDER BY MONTH(created_at);

类别 3：日期和时间算术

执行计算，例如添加或减去时间间隔，或查找两个日期之间的差异。

函数	描述
`DATE_ADD(date, INTERVAL expr unit)`	将指定的时间间隔添加到日期中。（同义词：`ADDDATE()`）。
`DATE_SUB(date, INTERVAL expr unit)`	从日期中减去指定的时间间隔。（同义词：`SUBDATE()`）。
`DATEDIFF(date1, date2)`	返回两个日期之间的天数。
`TIMESTAMPDIFF(unit, datetime1, datetime2)`	以指定单位（例如，`MINUTE`、`DAY`、`YEAR`）返回两个日期时间表达式之间的差值。
`LAST_DAY(date)`	返回给定日期所在月份的最后一天。

示例：计算订阅到期

-- 查找所有将在未来 30 天内到期的订阅
SELECT
    user_id,
    start_date,
    DATE_ADD(start_date, INTERVAL 1 YEAR) AS expiry_date
FROM subscriptions
WHERE DATE_ADD(start_date, INTERVAL 1 YEAR) BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY);

类别 4：格式化和解析

将日期/时间值转换为自定义字符串格式，或将字符串解析为日期/时间值。

函数	描述
`DATE_FORMAT(date, format)`	根据格式说明符字符串将日期格式化为字符串。
`STR_TO_DATE(str, format)`	根据格式说明符字符串将字符串解析为日期/时间值。
`FROM_UNIXTIME(unix_timestamp)`	将 UNIX 时间戳（自 ‘1970-01-01 00:00:00’ UTC 以来的秒数）转换为 DATETIME。
`UNIX_TIMESTAMP([date])`	返回给定日期的 UNIX 时间戳，如果未提供日期，则返回当前时间的时间戳。

示例：创建易读的报告日期

DATE_FORMAT 函数功能非常强大。常见的格式说明符包括 %Y（4 位年份）、%m（月份，01-12）、%d（日期，01-31）、%W（星期几名称）和 %H:%i:%s（时间）。请查阅 MySQL 文档以获取完整列表。

SELECT
    order_id,
    DATE_FORMAT(order_date, '%W, %M %e, %Y') AS formatted_date
FROM orders
LIMIT 5;

-- 示例输出：'Tuesday, December 5, 2023'

类别 5：时区和转换函数

处理不同时区和单位之间转换的基本函数。

函数	描述
`CONVERT_TZ(dt, from_tz, to_tz)`	将日期时间值从一个时区转换为另一个时区。需要加载时区表。
`TO_DAYS(date)`	给定一个日期，返回一个天数（从公元 0 年以来的天数）。
`FROM_DAYS(N)`	给定一个天数 N，返回一个 DATE 值。
`SEC_TO_TIME(seconds)`	将秒数转换为 ‘HH:MM:SS’ 时间格式。
`TIME_TO_SEC(time)`	将时间值转换为总秒数。

示例：将 UTC 转换为本地时区

-- 假设 event_time 以 UTC 存储
SELECT
    event_name,
    event_time AS time_in_utc,
    CONVERT_TZ(event_time, 'UTC', 'America/New_York') AS time_in_new_york
FROM events
LIMIT 1;

这仅是最常用函数的参考。有关完整和详细的列表，包括 MAKEDATE()、PERIOD_DIFF() 或 TO_SECONDS() 等不太常用的函数，请务必查阅官方 MySQL 文档，因为它是最新最准确的信息来源。