如何在PostgreSQL中使用EXTRACT函数从时间戳中提取指定年份或月份？

EXTRACT函数从时间类型中提取年月等字段，单位名须小写加单引号，返回double precision；正确语法为EXTRACT('unit' FROM time_value)；推荐范围查询替代WHERE中直接使用EXTRACT以提升索引效率。

EXTRACT 函数能直接从 TIMESTAMP 或 DATE 中取出年、月等字段，但要注意单位名称必须用小写字符串（如 'year'），且返回的是 double precision 类型，不是整数。

EXTRACT 的正确语法和单位写法

PostgreSQL 要求第二个参数是时间值，第一个参数是带单引号的字符串单位名。常见单位有 'year'、'month'、'day'、'hour' 等，全部小写，不能写成 YEAR 或 Year。

错误示例：EXTRACT(YEAR FROM now()) → 报错 ERROR: function extract("unknown", timestamp with time zone) does not exist

正确写法：

SELECT EXTRACT('year' FROM '2023-04-15'::DATE);  -- 返回 2023.0SELECT EXTRACT('month' FROM NOW());               -- 返回当前月份，如 6.0

• 单位名必须加单引号，且全小写
• 源值可以是 DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE，但不能是字符串字面量不带类型转换（如 '2023-04-15' 需转为 ::DATE）
• 结果总是 double precision，如需整数，显式转成 ::INTEGER

在 WHERE 条件中按年/月过滤数据

直接用 EXTRACT 做条件筛选效率不高，因为无法走索引（除非建函数索引）。更推荐用范围查询替代。

比如查 2022 年全年订单：

-- 不推荐（无法利用普通索引）WHERE EXTRACT('year' FROM order_time) = 2022<p>-- 推荐（可走索引）WHERE order_time >= '2022-01-01' AND order_time < '2023-01-01'

• 对高并发或大数据表，避免在 WHERE 中对时间列用 EXTRACT
• 如果必须用，可为常用提取字段建函数索引：CREATE INDEX idx_orders_year ON orders (EXTRACT('year' FROM order_time));
• EXTRACT('month' FROM ...) 单独使用几乎无业务意义，通常要结合年份一起判断（如“2022年6月”）

与 DATE_PART 的区别和兼容性注意

DATE_PART 是 PostgreSQL 的别名函数，行为和 EXTRACT 完全一致，参数顺序也相同：DATE_PART('year', now())。但它是 PostgreSQL 特有，其他数据库（如 MySQL、SQL Server）不支持。

• 两者返回值类型、精度、NULL 处理完全一样
• 若需跨数据库兼容，建议统一用标准 SQL 的 EXTRACT
• 不要混用大小写： date_part('YEAR', ...) 会报错，单位名仍须小写
• 注意时区影响：对 TIMESTAMP WITH TIME ZONE，EXTRACT 按会话时区解析，不是 UTC

真正容易被忽略的是类型隐式转换——比如把 EXTRACT('year' FROM col)::INTEGER 当成安全操作，但如果 col 是 NULL，结果仍是 NULL；而某些应用层 ORM 可能误判为 0 或空字符串，导致逻辑偏差。