EXTRACT() 函数详解：从日期时间中提取所需信息
大家好！今天我们来深入探讨 SQL 中一个非常实用的函数：EXTRACT()。这个函数允许我们从日期或时间值中提取特定的部分，比如年、月、日、小时、分钟等等。它在数据分析、报表生成以及任何需要细粒度日期时间操作的场景中都非常有用。
1. EXTRACT() 函数的基本语法
EXTRACT() 函数的语法如下：
EXTRACT(part FROM datetime_expression)其中：
part：指定要提取的日期或时间部分。它可以是以下值之一：YEARMONTHDAYHOURMINUTESECONDQUARTERWEEKDAYOFWEEK(星期几，通常 1 代表星期日，2 代表星期一，以此类推)DAYOFYEAR(一年中的第几天)EPOCH(自 1970-01-01 00:00:00 UTC 起的秒数)CENTURYDECADEMILLISECONDSMICROSECONDSTIMEZONE_HOURTIMEZONE_MINUTE
datetime_expression：要从中提取信息的日期或时间值。它可以是一个列名，一个表达式，或者一个返回日期或时间值的函数。
2. 常见的 part 及其用法示例
下面我们结合一些具体的例子，详细说明不同 part 的用法：
2.1 提取年份 (YEAR)
SELECT EXTRACT(YEAR FROM '2023-10-27'); -- 返回 2023如果 order_date 列包含订单日期，我们可以提取所有订单的年份：
SELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;这会返回一个包含所有订单年份的列表。
2.2 提取月份 (MONTH)
SELECT EXTRACT(MONTH FROM '2023-10-27'); -- 返回 10提取特定月份的订单：
SELECT *
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 10; -- 获取10月份的所有订单2.3 提取日 (DAY)
SELECT EXTRACT(DAY FROM '2023-10-27'); -- 返回 27查询某一天发生的事件：
SELECT event_name
FROM events
WHERE EXTRACT(DAY FROM event_date) = 27;2.4 提取小时 (HOUR)
SELECT EXTRACT(HOUR FROM '2023-10-27 14:30:00'); -- 返回 14分析一天中哪个小时的流量最高：
SELECT EXTRACT(HOUR FROM timestamp) AS hour, COUNT(*) AS event_count
FROM website_traffic
GROUP BY hour
ORDER BY event_count DESC;2.5 提取分钟 (MINUTE)
SELECT EXTRACT(MINUTE FROM '2023-10-27 14:30:00'); -- 返回 302.6 提取秒 (SECOND)
SELECT EXTRACT(SECOND FROM '2023-10-27 14:30:45'); -- 返回 452.7 提取季度 (QUARTER)
SELECT EXTRACT(QUARTER FROM '2023-02-15'); -- 返回 1
SELECT EXTRACT(QUARTER FROM '2023-04-15'); -- 返回 2
SELECT EXTRACT(QUARTER FROM '2023-07-15'); -- 返回 3
SELECT EXTRACT(QUARTER FROM '2023-11-15'); -- 返回 4按季度统计销售额：
SELECT EXTRACT(QUARTER FROM order_date) AS order_quarter, SUM(total_amount) AS total_sales
FROM orders
GROUP BY order_quarter
ORDER BY order_quarter;2.8 提取周 (WEEK)
注意：一周的起始日可能因数据库系统而异 (例如，MySQL 是周日，PostgreSQL 是周一)。
SELECT EXTRACT(WEEK FROM '2023-10-27'); -- 返回 43 (2023年的第43周)按周分析用户活跃度：
SELECT EXTRACT(WEEK FROM login_date) AS login_week, COUNT(DISTINCT user_id) AS active_users
FROM user_logins
GROUP BY login_week
ORDER BY login_week;2.9 提取星期几 (DAYOFWEEK)
DAYOFWEEK 返回的是一周中的第几天。 注意，不同的数据库系统对一周的起始日定义不同。 例如，在 PostgreSQL 中，1 代表星期日，2 代表星期一，以此类推。 在MySQL中，1代表星期日，2代表星期一，以此类推。
SELECT EXTRACT(DAYOFWEEK FROM '2023-10-27'); -- PostgreSQL: 返回 5 (星期五)
-- MySQL: 返回 6 (星期五)分析一周中哪一天的销售额最高：
SELECT EXTRACT(DAYOFWEEK FROM order_date) AS order_dayofweek, SUM(total_amount) AS total_sales
FROM orders
GROUP BY order_dayofweek
ORDER BY total_sales DESC;2.10 提取一年中的第几天 (DAYOFYEAR)
SELECT EXTRACT(DAYOFYEAR FROM '2023-01-01'); -- 返回 1
SELECT EXTRACT(DAYOFYEAR FROM '2023-02-01'); -- 返回 32
SELECT EXTRACT(DAYOFYEAR FROM '2023-12-31'); -- 返回 3652.11 提取 Epoch 时间 (EPOCH)
EPOCH 返回自 1970-01-01 00:00:00 UTC 以来的秒数。
SELECT EXTRACT(EPOCH FROM '2023-10-27 14:30:00'); -- 返回一个很大的数字 (秒数)在需要与时间戳进行数值比较时，EPOCH 很有用。
2.12 提取世纪 (CENTURY)
SELECT EXTRACT(CENTURY FROM '2023-10-27'); -- 返回 21
SELECT EXTRACT(CENTURY FROM '1999-12-31'); -- 返回 202.13 提取年代 (DECADE)
SELECT EXTRACT(DECADE FROM '2023-10-27'); -- 返回 2022.14 提取毫秒 (MILLISECONDS) 和微秒 (MICROSECONDS)
SELECT EXTRACT(MILLISECONDS FROM '2023-10-27 14:30:00.123'); -- 返回 123
SELECT EXTRACT(MICROSECONDS FROM '2023-10-27 14:30:00.123456'); -- 返回 1234562.15 提取时区小时 (TIMEZONE_HOUR) 和时区分钟 (TIMEZONE_MINUTE)
-- 示例，具体结果取决于数据库系统和时区设置
SELECT EXTRACT(TIMEZONE_HOUR FROM '2023-10-27 14:30:00+08');
SELECT EXTRACT(TIMEZONE_MINUTE FROM '2023-10-27 14:30:00+08');3. EXTRACT() 函数的应用场景
EXTRACT() 函数在各种场景中都非常有用：
- 数据分析： 按年、月、日等对数据进行分组和聚合。
- 报表生成： 提取日期和时间信息，用于报表的展示和筛选。
- 数据清洗： 从不规范的日期时间字符串中提取有效信息。
- 时间序列分析： 提取时间序列的特征，例如季节性。
- 业务逻辑： 根据日期和时间执行不同的业务规则。
4. 与其他日期时间函数的结合使用
EXTRACT() 函数通常与其他日期时间函数结合使用，以实现更复杂的功能。 例如：
-
DATE_TRUNC()(PostgreSQL): 将日期时间截断到指定的精度。SELECT DATE_TRUNC('month', order_date) AS month_start, SUM(total_amount) AS total_sales FROM orders GROUP BY month_start ORDER BY month_start; -
DATE_FORMAT()(MySQL): 将日期时间格式化为指定的字符串。SELECT DATE_FORMAT(order_date, '%Y-%m') AS order_month, SUM(total_amount) AS total_sales FROM orders GROUP BY order_month ORDER BY order_month; -
AGE()(PostgreSQL): 计算两个日期或时间之间的差。SELECT AGE(return_date, order_date) AS return_time FROM returns;
5. 不同数据库系统的差异
虽然 EXTRACT() 函数是 SQL 标准的一部分，但不同的数据库系统在实现上可能存在一些差异：
- 支持的
part： 不同的数据库系统可能支持不同的part值。 - 一周的起始日：
DAYOFWEEK的返回值可能因数据库系统而异。 - 时区处理： 不同数据库系统对时区的处理方式可能不同。
- 数据类型： 确保
datetime_expression的数据类型与数据库系统兼容。
在使用 EXTRACT() 函数时，务必查阅相应数据库系统的文档，了解其具体的实现细节。
6. 一些需要注意的点
- 性能： 在大型数据集上使用
EXTRACT()函数可能会影响性能。 可以考虑创建索引来优化查询。 - 数据类型： 确保
datetime_expression的数据类型正确。 如果是字符串，可能需要先将其转换为日期时间类型。 - NULL 值： 如果
datetime_expression为 NULL，EXTRACT()函数通常返回 NULL。 - 兼容性： 为了保证代码的可移植性，尽量使用 SQL 标准的
EXTRACT()函数，避免使用特定数据库系统的扩展。
7. 示例：计算每个月有多少天
我们可以结合 EXTRACT() 函数和数据库系统的其他函数来计算每个月有多少天。 以下是一个 PostgreSQL 的例子：
SELECT
EXTRACT(YEAR FROM dt) AS year,
EXTRACT(MONTH FROM dt) AS month,
(DATE_TRUNC('month', dt) + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS last_day_of_month,
EXTRACT(DAY FROM (DATE_TRUNC('month', dt) + INTERVAL '1 month' - INTERVAL '1 day')::DATE) AS days_in_month
FROM (SELECT '2023-01-15'::DATE AS dt UNION ALL
SELECT '2023-02-15'::DATE UNION ALL
SELECT '2023-03-15'::DATE) AS dates;这个查询首先选择了几个日期作为示例。然后，它使用 DATE_TRUNC() 函数将日期截断到月份，加上一个月，再减去一天，从而得到该月的最后一天。 最后，它使用 EXTRACT(DAY FROM ...) 函数提取该月的最后一天的日期，即该月的天数。
8. 示例：获取当前时间
虽然EXTRACT()函数主要用于从已有的日期时间值中提取信息，但我们可以结合其他函数来获取当前时间的各个部分。
-- PostgreSQL
SELECT
EXTRACT(YEAR FROM NOW()) AS current_year,
EXTRACT(MONTH FROM NOW()) AS current_month,
EXTRACT(DAY FROM NOW()) AS current_day,
EXTRACT(HOUR FROM NOW()) AS current_hour,
EXTRACT(MINUTE FROM NOW()) AS current_minute,
EXTRACT(SECOND FROM NOW()) AS current_second;
-- MySQL
SELECT
EXTRACT(YEAR FROM NOW()) AS current_year,
EXTRACT(MONTH FROM NOW()) AS current_month,
EXTRACT(DAY FROM NOW()) AS current_day,
EXTRACT(HOUR FROM NOW()) AS current_hour,
EXTRACT(MINUTE FROM NOW()) AS current_minute,
EXTRACT(SECOND FROM NOW()) AS current_second;
这里的NOW()函数返回当前日期和时间，然后EXTRACT()函数用于提取各个部分。
表格总结：EXTRACT() 函数常用 part
part |
描述 | 示例 |
|---|---|---|
YEAR |
年份 | EXTRACT(YEAR FROM '2023-10-27') 返回 2023 |
MONTH |
月份 | EXTRACT(MONTH FROM '2023-10-27') 返回 10 |
DAY |
日 | EXTRACT(DAY FROM '2023-10-27') 返回 27 |
HOUR |
小时 | EXTRACT(HOUR FROM '2023-10-27 14:30:00') 返回 14 |
MINUTE |
分钟 | EXTRACT(MINUTE FROM '2023-10-27 14:30:00') 返回 30 |
SECOND |
秒 | EXTRACT(SECOND FROM '2023-10-27 14:30:45') 返回 45 |
QUARTER |
季度 (1-4) | EXTRACT(QUARTER FROM '2023-02-15') 返回 1 |
WEEK |
周 (一年中的第几周) | EXTRACT(WEEK FROM '2023-10-27') 返回 43 |
DAYOFWEEK |
星期几 (注意不同数据库系统的起始日) | EXTRACT(DAYOFWEEK FROM '2023-10-27') |
DAYOFYEAR |
一年中的第几天 | EXTRACT(DAYOFYEAR FROM '2023-02-01') 返回 32 |
EPOCH |
自 1970-01-01 00:00:00 UTC 起的秒数 | EXTRACT(EPOCH FROM '2023-10-27 14:30:00') |
CENTURY |
世纪 | EXTRACT(CENTURY FROM '2023-10-27') 返回 21 |
DECADE |
年代 | EXTRACT(DECADE FROM '2023-10-27') 返回 202 |
MILLISECONDS |
毫秒 | EXTRACT(MILLISECONDS FROM '2023-10-27 14:30:00.123') |
MICROSECONDS |
微秒 | EXTRACT(MICROSECONDS FROM '2023-10-27 14:30:00.123456') |
TIMEZONE_HOUR |
时区小时 | EXTRACT(TIMEZONE_HOUR FROM '2023-10-27 14:30:00+08') |
TIMEZONE_MINUTE |
时区分钟 | EXTRACT(TIMEZONE_MINUTE FROM '2023-10-27 14:30:00+08') |
灵活运用，提取所需信息
EXTRACT() 函数是一个强大的工具，可以帮助我们从日期和时间值中提取各种信息。通过灵活运用 EXTRACT() 函数，我们可以更好地分析和处理数据，从而做出更明智的决策。
掌握要点，提升数据处理能力
EXTRACT()函数语法简单，功能强大。 掌握其用法，可以轻松提取日期时间的各个部分，提升数据处理的效率和准确性，并在实际工作中灵活应用。