PostgreSQL 中的 EXTRACT 函数_操作日期

0. 简介

在 PostgreSQL 中，EXTRACT 函数用于从日期/时间类型的数据中提取特定的部分，如年份、月份、星期几等。该函数非常适用于需要对时间字段进行分析或转换的场景。本文将详细介绍 EXTRACT 函数的用法，并提供丰富的案例，帮助你理解如何在 PostgreSQL 中高效地使用它。

1. EXTRACT 函数的语法

EXTRACT(field FROM source)

• **field**：要提取的时间字段，例如 YEAR、MONTH、DAY、HOUR 等。
• **source**：日期/时间数据，可以是 DATE、TIMESTAMP 或 INTERVAL 类型。

2. 常用 field参数

field 值	说明
YEAR	提取年份
MONTH	提取月份
DAY	提取日期（一个月中的第几天）
HOUR	提取小时
MINUTE	提取分钟
SECOND	提取秒
DOW	提取星期几（0=星期日，1=星期一…6=星期六）
DOY	提取一年中的第几天（1 到 366）
QUARTER	提取季度（1=第一季度，2=第二季度…）
EPOCH	提取自 1970-01-01 00:00:00 UTC 以来的秒数

3. EXTRACT 函数的案例

3.1 提取日期相关字段

1. 提取年份

SELECT EXTRACT(YEAR FROM TIMESTAMP '2025-02-05 15:30:00');

结果： 2025

2. 提取月份

SELECT EXTRACT(MONTH FROM TIMESTAMP '2025-02-05 15:30:00');

结果： 2

3. 提取日期（一个月中的第几天）

SELECT EXTRACT(DAY FROM TIMESTAMP '2025-02-05 15:30:00');

结果： 5

3.2 提取时间相关字段

4. 提取小时

SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-02-05 15:30:00');

结果： 15

5. 提取分钟

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2025-02-05 15:30:00');

结果： 30

6. 提取秒

SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-02-05 15:30:45.123456');

结果： 45.123456

3.3 提取周、季度、年度相关信息

7. 提取星期几（DOW）

SELECT EXTRACT(DOW FROM DATE '2025-02-05');

结果： 3（星期三）

8. 提取一年中的第几天（DOY）

SELECT EXTRACT(DOY FROM DATE '2025-02-05');

结果： 36（2025 年 2 月 5 日是一年中的第 36 天）

9. 提取季度

SELECT EXTRACT(QUARTER FROM DATE '2025-02-05');

结果： 1（第一季度）

3.4 提取 Unix 时间戳（EPOCH）

10. 计算自 1970-01-01 以来的秒数

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-02-05 15:30:00');

结果： 一个很大的整数，表示自 1970 年 1 月 1 日以来的秒数。

11. 计算两个时间点之间的秒数

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-02-05 16:00:00') - EXTRACT(EPOCH FROM TIMESTAMP '2025-02-05 15:30:00');

结果： 1800（两者相差 1800 秒，即 30 分钟）

4. EXTRACT 与 DATE_PART`的区别

EXTRACT 和 DATE_PART 都可以用来提取日期/时间的特定部分，但 DATE_PART 返回的是 DOUBLE PRECISION 类型，而 EXTRACT 适用于更多的时间单位。

示例对比：

-- 使用 EXTRACT
SELECT EXTRACT(YEAR FROM TIMESTAMP '2025-02-05 15:30:00');

-- 使用 DATE_PART
SELECT DATE_PART('year', TIMESTAMP '2025-02-05 15:30:00');

区别：

• EXTRACT 使用 SQL 标准的 field 关键字。
• DATE_PART 需要使用引号包裹 field 参数，并返回 DOUBLE PRECISION。

5. 结论

PostgreSQL 的 EXTRACT 函数是一个强大的日期/时间处理工具，可以提取各种时间字段，在数据分析、查询优化、报表统计等场景中非常有用。