postgreSql日期函数及功能说明
在 PostgreSQL 中，日期和时间处理是数据库操作的核心功能之一。以下是常用的日期函数分类及详细说明，帮助您高效操作时间数据：
一、获取当前日期和时间
函数 返回类型 描述 示例
CURRENT_DATE DATE 当前日期（无时间） 2023-10-05
CURRENT_TIME TIME WITH TIME ZONE 当前时间（带时区） 14:30:45.123+08
CURRENT_TIMESTAMP TIMESTAMP WITH TIME ZONE 当前日期和时间（带时区） 2023-10-05 14:30:45.123+08
NOW() TIMESTAMP WITH TIME ZONE 同 CURRENT_TIMESTAMP 同上
LOCALTIME TIME 当前时间（不带时区） 14:30:45.123
LOCALTIMESTAMP TIMESTAMP 当前日期和时间（不带时区） 2023-10-05 14:30:45.123
二、日期运算
1. 加减时间间隔
使用 + 或 - 配合 INTERVAL 关键字：
-- 加 7 天
SELECT CURRENT_DATE + INTERVAL '7 days'; -- 结果：2023-10-12
-- 减 3 小时
SELECT NOW() - INTERVAL '3 hours'; -- 结果：当前时间减 3 小时
2. 计算日期差
使用 AGE() 函数：
-- 计算两个日期的年龄差（返回 INTERVAL）
SELECT AGE('2023-10-05', '2000-05-15'); -- 结果：23 years 4 mons 20 days
-- 计算到当前日期的年龄
SELECT AGE('2000-05-15'); -- 结果：23 years 4 mons 20 days
三、日期格式化
1. 日期转字符串
使用 TO_CHAR()：
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- 结果：2023-10-05 14:30:45
SELECT TO_CHAR(CURRENT_DATE, 'Day, DD Mon YYYY'); -- 结果：Thursday, 05 Oct 2023
2. 字符串转日期
使用 TO_DATE() 或 TO_TIMESTAMP()：
SELECT TO_DATE('20231005', 'YYYYMMDD'); -- 结果：2023-10-05 (DATE)
SELECT TO_TIMESTAMP('20231005 143045', 'YYYYMMDD HH24MISS'); -- 结果：TIMESTAMP
四、提取日期部分
1. EXTRACT()
提取特定时间单位（如年、月、日）的值：
SELECT
EXTRACT(YEAR FROM NOW()) AS year, -- 2023
EXTRACT(MONTH FROM NOW()) AS month, -- 10
EXTRACT(DAY FROM NOW()) AS day; -- 5
2. DATE_PART()
功能同 EXTRACT，但参数顺序不同：
SELECT DATE_PART('hour', NOW()) AS hour; -- 14
五、日期截断
使用 DATE_TRUNC() 将日期截断到指定精度：
SELECT DATE_TRUNC('month', NOW()); -- 2023-10-01 0（保留到月份）
SELECT DATE_TRUNC('hour', NOW()); -- 2023-10-05 14:00:00（保留到小时）
六、时区转换
1. 转换时区
使用 AT TIME ZONE：
-- 将带时区的时间转换为另一时区
SELECT NOW() AT TIME ZONE 'Asia/Shanghai'; -- 转为上海时间（TIMESTAMP）
-- 将不带时区的时间视为某时区并转换
SELECT TIMESTAMP '2023-10-05 14:30:45' AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo';
2. 设置会话时区
SET TIME ZONE 'Asia/Shanghai'; -- 设置当前会话时区
七、生成日期序列
使用 generate_series() 生成连续日期：
-- 生成 2023-10-01 到 2023-10-05 的日期序列
SELECT generate_series(
'2023-10-01'::DATE,
'2023-10-05'::DATE,
INTERVAL '1 day'
) AS date;
八、验证日期有效性
使用 ISDATE()（需自定义函数）或隐式转换：
-- 自定义 ISDATE 函数
CREATE OR REPLACE FUNCTION is_date(s TEXT) RETURNS BOOLEAN AS $$
BEGIN
PERFORM s::DATE;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
SELECT is_date('2023-13-01'); -- false（无效日期）
九、常用场景示例
1. 查询最近 7 天的订单
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';
2. 格式化日志时间
SELECT log_id, TO_CHAR(log_time, 'YYYY-MM-DD HH24:MI:SS') AS formatted_time
FROM logs;
3. 计算用户年龄
SELECT user_id, AGE(birth_date) AS age FROM users;
十、注意事项
时区陷阱
使用 TIMESTAMP WITH TIME ZONE 存储时间，避免时区混乱。
插入数据时显式指定时区（如 2023-10-05 14:30+08）。
性能优化
对日期字段创建索引以加速范围查询：
CREATE INDEX idx_orders_date ON orders (order_date);
格式一致性
使用 ISO 8601 格式（YYYY-MM-DD HH24:MI:SS）减少歧义。
掌握这些日期函数，您能高效处理时间数据，满足复杂业务场景需求。建议结合业务需求灵活组合使用！
查看更多关于postgreSql日期函数及功能说明的详细内容...