PGSQL日期函数怎么用？日期函数购买指南及操作技巧

在PostgreSQL（简称PGSQL）中，日期函数是处理日期和时间数据的核心工具，广泛应用于数据查询、报表生成、业务逻辑实现等场景，掌握这些函数的使用方法，能够高效解决与日期相关的各类需求，本文将详细介绍PGSQL中常用日期函数的功能、语法及实际应用场景,帮助用户快速上手并灵活运用。

基础日期函数：获取当前日期与时间

在处理日期数据时，首先需要获取当前时间或特定时间点的日期信息，PGSQL提供了多个基础函数用于获取当前日期、时间及时间戳：

• CURRENT_DATE：返回当前日期（仅包含年、月、日），格式为YYYYMMDD。SELECT CURRENT_DATE; 结果可能为20251015。
• CURRENT_TIME：返回当前时间（仅包含时、分、秒、毫秒），格式为HH:MM:SS.MS。SELECT CURRENT_TIME; 结果可能为14:30:25.123456。
• CURRENT_TIMESTAMP：返回当前日期和时间（包含时区信息），格式为YYYYMMDD HH:MM:SS.MS+TZ。SELECT CURRENT_TIMESTAMP; 结果可能为20251015 14:30:25.123456+08。
• LOCALTIMESTAMP：与CURRENT_TIMESTAMP类似，但不包含时区信息，格式为YYYYMMDD HH:MM:SS.MS。SELECT LOCALTIMESTAMP; 结果可能为20251015 14:30:25.123456。

应用场景：在数据表中记录创建时间、查询数据生成时间快照等，在用户表中添加created_at字段，默认值设为CURRENT_TIMESTAMP,即可自动记录用户注册时间。

日期格式化函数：按需转换日期显示格式

实际业务中，日期的显示格式可能需要根据需求调整，PGSQL通过TO_CHAR()函数实现日期与字符串的格式化转换，其语法为TO_CHAR(date, format)，其中format为格式模板，常用模板如表1所示：

表1：TO_CHAR()函数常用格式模板

| 模板 | 说明 | 示例（输入日期：20251015 14:30:25） |

||||

| YYYY | 四位年份 | 2025 |

| MM | 两位月份（0112） | 10 |

| DD | 两位日期（0131） | 15 |

| HH24 | 24小时制小时（0023） | 14 |

| MI | 分钟（0059） | 30 |

| SS | 秒（0059） | 25 |

| DY | 星期缩写（如Sun、Mon） | Sun |

| MONTH | 月份全称（大写，如OCTOBER） | OCTOBER |

| YYYYMMDD HH24:MI:SS | 完整日期时间格式 | 20251015 14:30:25 |

示例：

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY年MM月DD日 HH24时MI分SS秒') AS formatted_time;

结果可能为2025年10月15日 14时30分25秒。

应用场景：生成用户友好的日期显示格式，如报表标题、系统日志记录等。

日期解析函数：字符串转换为日期

当字符串格式的日期需要转换为日期类型时，可使用TO_DATE()或TO_TIMESTAMP()函数，语法分别为TO_DATE(string, format)和TO_TIMESTAMP(string, format)。

示例：

SELECT TO_DATE('20251015', 'YYYYMMDD') AS date_val; 结果：20251015（日期类型） SELECT TO_TIMESTAMP('20251015 14:30:25', 'YYYYMMDD HH24:MI:SS') AS timestamp_val; 结果：20251015 14:30:25（时间戳类型）

注意事项：字符串格式必须与format模板严格匹配，否则会报错。'2025/10/15'需使用'YYYY/MM/DD'格式解析。

应用场景：处理用户输入的日期字符串（如表单提交的日期）、导入外部数据时的日期格式转换等。

日期计算函数：加减日期与时间差

日期加减运算

PGSQL支持直接使用和运算符对日期与时间间隔进行计算，常用时间间隔关键字如表2所示：

表2：常用时间间隔关键字

| 关键字 | 说明 | 示例 |

||||

| YEAR | 年 | INTERVAL '1' YEAR |

| MONTH | 月 | INTERVAL '2' MONTH |

| DAY | 日 | INTERVAL '7' DAY |

| HOUR | 小时 | INTERVAL '3' HOUR |

| MINUTE | 分钟 | INTERVAL '30' MINUTE |

| SECOND | 秒 | INTERVAL '45' SECOND |

示例：

当前日期加10天 SELECT CURRENT_DATE + INTERVAL '10' DAY AS future_date; 当前时间戳减3个月 SELECT CURRENT_TIMESTAMP INTERVAL '3' MONTH AS past_date; 日期加减数值（数值默认为天） SELECT DATE '20251015' + 5 AS new_date; 结果：20251020

计算日期差

使用AGE()函数或运算符可计算两个日期之间的间隔，AGE()函数返回interval类型，包含年、月、日信息；运算符返回天数差。

示例：

计算当前日期与2025年1月1日的间隔 SELECT AGE(CURRENT_DATE, '20250101') AS date_diff; 计算20251015与20251001的天数差 SELECT DATE '20251015' DATE '20251001' AS day_diff; 结果：14

应用场景：计算订单到期时间、用户年龄、项目周期天数等。

日期提取函数：获取日期中的部分信息

若需从日期中提取年、月、日、小时等部分信息，可使用EXTRACT()函数，语法为EXTRACT(unit FROM date)，unit为提取的单位，如YEAR、MONTH、DAY、HOUR等。

示例：

提取当前日期的年份 SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS year_val; 结果：2025 提取当前时间的小时 SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour_val; 结果：14（假设当前时间为14:30:25） 计算当前日期是该年第几天 SELECT EXTRACT(DOY FROM CURRENT_DATE) AS day_of_year; 结果：288（2025年10月15日）

应用场景：按年、月、日分组统计（如统计每月销售额）、筛选特定时间范围的数据等。

高级日期函数：处理时区与闰年

时区转换

使用AT TIME ZONE或CONVERT_TIMEZONE()（需安装pgtz扩展）可转换时间戳的时区。

示例：

将当前UTC时间转换为北京时间（东八区） SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' AS shanghai_time;

闰年判断

通过EXTRACT()函数提取年份，结合模运算判断闰年：若年份能被4整除且不能被100整除，或能被400整除，则为闰年。

示例：

SELECT CASE WHEN EXTRACT(YEAR FROM DATE '20250101') % 4 = 0 AND (EXTRACT(YEAR FROM DATE '20250101') % 100 != 0 OR EXTRACT(YEAR FROM DATE '20250101') % 400 = 0) THEN '闰年' ELSE '平年' END AS leap_year;

相关问答FAQs

Q1: 如何计算两个日期之间的工作日天数（排除周末）？

A1: 可通过自定义函数实现，核心逻辑是遍历两个日期之间的每一天，使用EXTRACT(ISODOW FROM date)判断是否为周末（值为6或7），并累加工作日，示例函数如下：

CREATE OR REPLACE FUNCTION working_days(start_date DATE, end_date DATE) RETURNS INT AS $$ DECLARE days INT := 0; current_date DATE := start_date; BEGIN WHILE current_date <= end_date LOOP IF EXTRACT(ISODOW FROM current_date) BETWEEN 1 AND 5 THEN days := days + 1; END IF; current_date := current_date + INTERVAL '1' DAY; END LOOP; RETURN days; END; $$ LANGUAGE plpgsql; 使用示例：SELECT working_days('20251001', '20251015');

Q2: 如何将日期格式化为“YYYY年MM月DD日 星期X”的形式？

A2: 使用TO_CHAR()函数结合格式模板YYYY年MM月DD日 DAY，其中DAY返回星期全称（如星期一），示例：

SELECT TO_CHAR(CURRENT_DATE, 'YYYY年MM月DD日 DAY') AS formatted_date; 结果示例：2025年10月15日 星期日