课程 3.4: SQL 中的基本日期和时间函数
SQL 中的日期和时间函数允许您提取、修改和格式化日期和时间值。这些函数广泛用于分析时间数据、按日期过滤、计算间隔和格式化输出。本课程涵盖了最常用的函数，并基于 Sakila 数据库提供示例。
重要提示: CURRENT_DATE、CURRENT_TIME 和 CURRENT_TIMESTAMP 在许多数据库管理系统中是特殊的 SQL 表达式（或相应函数的别名），而不是以 NAME(arg1, arg2, ...) 形式的“常规”函数。因此，语法和行为细节在不同的数据库管理系统中可能会有所不同。
常见的日期和时间函数
CURRENT_DATE — 一个特殊表达式，返回当前日期（不包含时间）。
语法:
CURRENT_DATE
示例:
SELECT CURRENT_DATE AS today;
结果: 当前日期，例如：2025-06-03
CURRENT_TIME — 一个特殊表达式/别名，返回当前时间（不包含日期）。
语法:
CURRENT_TIME
CURRENT_TIME()
CURRENT_TIME(precision)
示例:
SELECT CURRENT_TIME AS now_time;
结果: 当前时间。如果指定了精度（例如，CURRENT_TIME(3)），则值包括小数秒。
CURRENT_TIMESTAMP / NOW() — 返回当前日期和时间（通常作为特殊表达式/别名）。
语法:
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP(precision)
NOW()
示例:
SELECT CURRENT_TIMESTAMP AS now_datetime;
SELECT NOW() AS now_datetime;
结果: 当前日期和时间，例如：2025-06-03 14:25:30
重要提示: 在大多数数据库管理系统中，CURRENT_DATE/CURRENT_TIME/CURRENT_TIMESTAMP 在语句执行开始时固定（在某些模式下，在事务开始时）。因此，在一个查询中，所有行通常会获得相同的值。
如果您需要在特定行的函数评估时获取“当前时间戳”，请使用特定于数据库管理系统的替代方案（例如，在 MySQL/MariaDB 中使用 SYSDATE()，在 PostgreSQL 中使用 clock_timestamp()）。
DATE() — 从日期时间值中提取仅日期。
语法:
DATE(datetime_value)
示例:
SELECT DATE(rental_date) AS rental_only_date
FROM rental
LIMIT 3;
结果: 仅返回 rental_date 列中的日期。
TIME() — 从日期时间值中提取仅时间。
语法:
TIME(datetime_value)
示例:
SELECT TIME(rental_date) AS rental_only_time
FROM rental
LIMIT 3;
结果: 仅返回 rental_date 列中的时间。
YEAR() — 从日期值中提取年份。
语法:
YEAR(date_value)
示例:
SELECT YEAR(rental_date) AS rental_year
FROM rental
LIMIT 3;
结果: 返回租赁日期的年份。
MONTH() — 从日期值中提取月份。
语法:
MONTH(date_value)
示例:
SELECT MONTH(rental_date) AS rental_month
FROM rental
LIMIT 3;
结果: 返回租赁日期的月份。
DAY() — 从日期值中提取月份中的天数。
语法:
DAY(date_value)
示例:
SELECT DAY(rental_date) AS rental_day
FROM rental
LIMIT 3;
结果: 返回租赁日期中的天数。
DATE_ADD() — 向日期添加指定的间隔。
语法:
DATE_ADD(date, INTERVAL value unit)
示例:
SELECT DATE_ADD(rental_date, INTERVAL 7 DAY) AS return_due
FROM rental
LIMIT 3;
结果: 返回增加 7 天后的日期。
DATE_SUB() — 从日期中减去指定的间隔。
语法:
DATE_SUB(date, INTERVAL value unit)
示例:
SELECT DATE_SUB(rental_date, INTERVAL 3 DAY) AS three_days_before
FROM rental
LIMIT 3;
结果: 返回减少 3 天后的日期。
DATEDIFF() — 返回两个日期之间的天数。
语法:
DATEDIFF(date1, date2)
示例:
SELECT DATEDIFF(return_date, rental_date) AS rental_duration
FROM rental
WHERE return_date IS NOT NULL
LIMIT 3;
结果: 返回归还日期和租赁日期之间的天数。
DATE_FORMAT() — 以指定格式格式化日期（MySQL）。
语法:
DATE_FORMAT(date, format)
示例:
SELECT DATE_FORMAT(rental_date, '%d.%m.%Y') AS formatted_date
FROM rental
LIMIT 3;
结果: 日期格式为 dd.mm.yyyy，例如：03.06.2025
常见格式说明符:
%Y: 年（4 位数字）%m: 月（2 位数字）%d: 月中的天（2 位数字）%H: 小时（24 小时制）%i: 分钟%s: 秒
STRFTIME() — 格式化日期/时间（SQLite、PostgreSQL）。
语法:
STRFTIME(format, date)
示例:
SELECT STRFTIME('%Y-%m-%d', rental_date) AS formatted_date
FROM rental
LIMIT 3;
结果: 日期格式为 yyyy-mm-dd。
TIMESTAMPDIFF() — 以指定单位计算两个日期/时间之间的差异（MySQL）。
语法:
TIMESTAMPDIFF(unit, datetime1, datetime2)
示例:
SELECT TIMESTAMPDIFF(DAY, rental_date, return_date) AS days_rented
FROM rental
WHERE return_date IS NOT NULL
LIMIT 3;
结果: 返回租赁日期和归还日期之间的天数。
EXTRACT() — 提取日期或时间的一部分（年、月、日等）。
语法:
EXTRACT(part FROM date)
示例:
SELECT EXTRACT(YEAR FROM rental_date) AS rental_year
FROM rental
LIMIT 3;
结果: 从租赁日期中提取年份。
实际应用
- 查找过去 30 天内租赁的电影:
SELECT * FROM rental WHERE rental_date > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY); - 按月计算租赁次数:
SELECT YEAR(rental_date) AS year, MONTH(rental_date) AS month, COUNT(*) AS rentals FROM rental GROUP BY year, month ORDER BY year DESC, month DESC; - 为报告格式化租赁日期:
SELECT DATE_FORMAT(rental_date, '%d.%m.%Y') AS formatted_rental FROM rental LIMIT 5;
本课的关键要点
日期和时间函数允许您灵活地分析和转换 SQL 中的时间数据。使用它们进行过滤、分组、计算间隔和格式化报告中的日期。通过使用 Sakila 数据库中的示例练习这些函数，以巩固您的技能。