PostgreSQL 日期/时间函数和操作符

PostgreSQL 提供了丰富的日期/时间函数和操作符，用于处理各种时间相关的操作。本章节将详细介绍这些函数的使用方法和实际应用场景。

概述

PostgreSQL 支持多种日期/时间数据类型，包括：

• date：日期（年-月-日）
• time：时间（时:分:秒）
• timestamp：时间戳（日期+时间）
• interval：时间间隔
• 带时区和不带时区的变体

INFO

重要提示所有接收 time 或 timestamp 输入的函数都有两种变体：

• 带时区版本：time with time zone 或 timestamp with time zone
• 不带时区版本：time without time zone 或 timestamp without time zone

日期/时间操作符

基本算术操作

日期/时间类型支持多种算术操作，让我们通过实际例子来理解：

日期加法操作

日期加天数

-- 向日期添加天数
SELECT date '2001-09-28' + 7 AS result;
-- 结果: 2001-10-05

-- 实际应用：计算订单预计到达日期
SELECT
    order_id,
    order_date,
    order_date + delivery_days AS estimated_delivery
FROM orders
WHERE order_date >= '2024-01-01';

日期加间隔

-- 向日期添加间隔
SELECT date '2001-09-28' + interval '1 hour' AS result;
-- 结果: 2001-09-28 01:00:00

-- 实际应用：会议安排
SELECT
    meeting_title,
    start_date + interval '2 hours' AS end_time
FROM meetings
WHERE start_date = CURRENT_DATE;

日期加时间

-- 向日期添加一天中的时间
SELECT date '2001-09-28' + time '03:00' AS result;
-- 结果: 2001-09-28 03:00:00

-- 实际应用：创建完整的事件时间戳
SELECT
    event_name,
    event_date + start_time AS event_datetime
FROM events
WHERE event_date BETWEEN '2024-06-01' AND '2024-06-30';

时间戳和间隔操作

-- 时间戳加间隔
SELECT timestamp '2001-09-28 01:00' + interval '23 hours' AS result;
-- 结果: 2001-09-29 00:00:00

-- 间隔相加
SELECT interval '1 day' + interval '1 hour' AS result;
-- 结果: 1 day 01:00:00

-- 实际应用：计算员工工作时长
SELECT
    employee_id,
    start_time,
    start_time + work_duration AS end_time,
    work_duration
FROM employee_schedule
WHERE work_date = CURRENT_DATE;

减法操作

-- 日期相减，得到天数差
SELECT date '2001-10-01' - date '2001-09-28' AS days_diff;
-- 结果: 3

-- 时间戳相减，得到间隔
SELECT timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' AS time_diff;
-- 结果: 63 days 15:00:00

-- 实际应用：计算项目持续时间
SELECT
    project_name,
    end_date - start_date AS project_duration_days,
    end_date - start_date || ' days' AS formatted_duration
FROM projects
WHERE status = 'completed';

间隔的乘除运算

-- 间隔乘法
SELECT interval '1 second' * 900 AS result;
-- 结果: 00:15:00

SELECT interval '1 hour' * 3.5 AS result;
-- 结果: 03:30:00

-- 间隔除法
SELECT interval '1 hour' / 1.5 AS result;
-- 结果: 00:40:00

-- 实际应用：计算加班费时长
SELECT
    employee_id,
    base_overtime * overtime_multiplier AS total_overtime
FROM (
    SELECT
        employee_id,
        interval '2 hours' AS base_overtime,
        1.5 AS overtime_multiplier
    FROM employee_overtime
) AS overtime_calc;

操作符汇总表

操作符	描述	示例	结果
date + integer	向日期添加天数	date '2001-09-28' + 7	2001-10-05
date + interval	向日期添加间隔	date '2001-09-28' + interval '1 hour'	2001-09-28 01:00:00
date + time	向日期添加时间	date '2001-09-28' + time '03:00'	2001-09-28 03:00:00
date - date	日期相减得天数	date '2001-10-01' - date '2001-09-28'	3
timestamp - timestamp	时间戳相减得间隔	timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'	63 days 15:00:00
interval * numeric	间隔乘以数值	interval '1 hour' * 3.5	03:30:00
interval / numeric	间隔除以数值	interval '1 hour' / 1.5	00:40:00

日期/时间函数详解

年龄计算函数

age() 函数

age() 函数用于计算两个时间戳之间的年龄差，结果以年、月、日的形式表示。

-- 计算两个日期之间的年龄差
SELECT age(timestamp '2001-04-10', timestamp '1957-06-13') AS age_diff;
-- 结果: 43 years 9 mons 27 days

-- 计算从指定日期到当前日期的年龄
SELECT age(timestamp '1990-05-15') AS current_age;
-- 结果: 约 34 years 0 mons 19 days

-- 实际应用：员工年龄统计
SELECT
    employee_id,
    name,
    birth_date,
    age(birth_date) AS current_age,
    EXTRACT(year FROM age(birth_date)) AS age_years
FROM employees
ORDER BY age(birth_date) DESC;

分析过程：

• age() 函数会智能地处理月份和年份的计算
• 结果格式为 "X years Y mons Z days"
• 适用于需要精确年龄计算的场景，如 HR 系统、保险计算等

当前时间函数

PostgreSQL 提供多种获取当前时间的函数，它们在事务处理和精度方面有所不同：

事务级时间函数

-- current_timestamp: 返回当前事务开始时的时间戳
SELECT current_timestamp AS transaction_time;
-- 结果: 2024-06-03 14:39:53.662522+08

-- current_date: 返回当前日期
SELECT current_date AS today;
-- 结果: 2024-06-03

-- current_time: 返回当前时间（带时区）
SELECT current_time AS now_time;
-- 结果: 14:39:53.662522+08

-- 带精度限制的版本
SELECT
    current_timestamp(0) AS ts_no_decimal,
    current_time(2) AS time_2_decimal;
-- 结果: 2024-06-03 14:39:53+08, 14:39:53.66+08

实时时间函数

-- clock_timestamp: 返回当前实际时间（会在语句执行期间变化）
SELECT
    clock_timestamp() AS real_time_1,
    pg_sleep(1),  -- 休眠1秒
    clock_timestamp() AS real_time_2;
-- 两个时间戳会有约1秒的差异

-- 实际应用：记录操作的精确时间
INSERT INTO audit_log (
    operation_type,
    operation_time,
    user_id
) VALUES (
    'login',
    clock_timestamp(),
    123
);

本地时间函数

-- localtime 和 localtimestamp: 返回本地时间（不带时区信息）
SELECT
    localtime AS local_time_now,
    localtimestamp AS local_timestamp_now;
-- 结果: 14:39:53.662522, 2024-06-03 14:39:53.662522

-- 实际应用：生成本地时间报告
SELECT
    report_id,
    generated_at,
    localtimestamp AS local_generation_time
FROM reports
WHERE generated_at >= current_date;

时间函数对比表：

函数	是否带时区	是否实时更新	事务一致性	使用场景
current_timestamp	是	否	是	事务日志、数据一致性要求高的场景
clock_timestamp()	是	是	否	性能监控、精确计时
localtimestamp	否	否	是	本地时间报告
current_date	否	否	是	日期过滤、日报生成

日期构造函数

make_date(), make_time(), make_timestamp()

这些函数允许从单独的数值组件构造日期和时间：

-- 从年、月、日创建日期
SELECT make_date(2024, 6, 15) AS constructed_date;
-- 结果: 2024-06-15

-- 从小时、分钟、秒创建时间
SELECT make_time(8, 30, 45.5) AS constructed_time;
-- 结果: 08:30:45.5

-- 创建完整的时间戳
SELECT make_timestamp(2024, 6, 15, 8, 30, 45.5) AS constructed_timestamp;
-- 结果: 2024-06-15 08:30:45.5

-- 实际应用：动态构建日期时间
SELECT
    event_id,
    make_date(event_year, event_month, event_day) AS event_date,
    make_time(start_hour, start_minute, 0) AS start_time
FROM event_schedule
WHERE event_year = 2024;

make_interval()

-- 创建间隔
SELECT make_interval(days => 10) AS ten_days;
-- 结果: 10 days

SELECT make_interval(years => 1, months => 6, days => 15, hours => 3) AS complex_interval;
-- 结果: 1 year 6 mons 15 days 03:00:00

-- 实际应用：动态计算到期时间
SELECT
    subscription_id,
    start_date,
    start_date + make_interval(months => duration_months) AS end_date
FROM subscriptions
WHERE status = 'active';

时区操作函数

date_add() 和 date_subtract()

这些函数在处理时区变化时特别有用：

-- 在特定时区中添加时间
SELECT date_add(
    '2021-10-31 00:00:00+02'::timestamptz,
    '1 day'::interval,
    'Europe/Warsaw'
) AS result;
-- 结果: 2021-10-31 23:00:00+00

-- 从时间戳中减去间隔（考虑时区）
SELECT date_subtract(
    '2021-11-01 00:00:00+01'::timestamptz,
    '1 day'::interval,
    'Europe/Warsaw'
) AS result;
-- 结果: 2021-10-30 22:00:00+00

-- 实际应用：国际业务的时间计算
SELECT
    event_id,
    event_name,
    utc_time,
    date_add(utc_time, duration, local_timezone) AS local_end_time
FROM international_events
WHERE event_date = CURRENT_DATE;

时间提取和截断

EXTRACT() 和 date_part() 函数

用于从日期/时间值中提取特定的字段：

-- 提取时间戳的各个部分
SELECT
    extract(year from timestamp '2001-02-16 20:38:40') AS year_part,
    extract(month from timestamp '2001-02-16 20:38:40') AS month_part,
    extract(day from timestamp '2001-02-16 20:38:40') AS day_part,
    extract(hour from timestamp '2001-02-16 20:38:40') AS hour_part;
-- 结果: 2001, 2, 16, 20

-- date_part 函数（等效于 extract）
SELECT date_part('hour', timestamp '2001-02-16 20:38:40') AS hour_value;
-- 结果: 20

-- 从间隔中提取值
SELECT
    extract(year from interval '2 years 3 months') AS interval_years,
    extract(month from interval '2 years 3 months') AS interval_months;
-- 结果: 2, 3

-- 实际应用：销售数据按月统计
SELECT
    extract(year from sale_date) AS sale_year,
    extract(month from sale_date) AS sale_month,
    count(*) AS sales_count,
    sum(amount) AS total_amount
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY extract(year from sale_date), extract(month from sale_date)
ORDER BY sale_year, sale_month;

date_trunc() 函数

用于将日期/时间截断到指定的精度：

-- 截断到小时
SELECT date_trunc('hour', timestamp '2001-02-16 20:38:40') AS truncated_hour;
-- 结果: 2001-02-16 20:00:00

-- 截断到天
SELECT date_trunc('day', timestamp '2001-02-16 20:38:40') AS truncated_day;
-- 结果: 2001-02-16 00:00:00

-- 截断到月
SELECT date_trunc('month', timestamp '2001-02-16 20:38:40') AS truncated_month;
-- 结果: 2001-02-01 00:00:00

-- 在指定时区中截断
SELECT date_trunc(
    'day',
    timestamptz '2001-02-16 20:38:40+00',
    'Australia/Sydney'
) AS truncated_in_timezone;
-- 结果: 2001-02-16 13:00:00+00

-- 截断间隔
SELECT date_trunc('hour', interval '2 days 3 hours 40 minutes') AS truncated_interval;
-- 结果: 2 days 03:00:00

-- 实际应用：按小时统计网站访问量
SELECT
    date_trunc('hour', access_time) AS hour_period,
    count(*) AS access_count
FROM website_logs
WHERE access_time >= CURRENT_DATE - interval '7 days'
GROUP BY date_trunc('hour', access_time)
ORDER BY hour_period;

date_bin() 函数

将时间戳分组到指定的间隔桶中：

-- 将时间戳分组到15分钟的间隔中
SELECT date_bin(
    '15 minutes'::interval,
    timestamp '2001-02-16 20:38:40',
    timestamp '2001-02-16 20:05:00'
) AS binned_time;
-- 结果: 2001-02-16 20:35:00

-- 实际应用：按5分钟间隔统计API调用
SELECT
    date_bin('5 minutes'::interval, request_time, '2024-06-03 00:00:00'::timestamp) AS time_bucket,
    count(*) AS request_count,
    avg(response_time) AS avg_response_time
FROM api_logs
WHERE request_time >= CURRENT_DATE
GROUP BY time_bucket
ORDER BY time_bucket;

间隔调整函数

justify_* 系列函数

这些函数用于标准化间隔值的表示：

-- justify_days: 将30天转换为月
SELECT justify_days(interval '1 year 65 days') AS justified_days;
-- 结果: 1 year 2 mons 5 days

-- justify_hours: 将24小时转换为天
SELECT justify_hours(interval '50 hours 10 minutes') AS justified_hours;
-- 结果: 2 days 02:10:00

-- justify_interval: 综合调整
SELECT justify_interval(interval '1 mon -1 hour') AS justified_interval;
-- 结果: 29 days 23:00:00

-- 实际应用：标准化员工工作时长记录
SELECT
    employee_id,
    justify_hours(total_work_time) AS standardized_work_time
FROM (
    SELECT
        employee_id,
        sum(work_duration) AS total_work_time
    FROM employee_timesheet
    WHERE work_date BETWEEN '2024-06-01' AND '2024-06-30'
    GROUP BY employee_id
) AS monthly_hours;

有限性检查函数

isfinite() 函数

检查日期、时间戳或间隔是否为有限值：

-- 检查日期是否有限
SELECT isfinite(date '2001-02-16') AS is_date_finite;
-- 结果: true

-- 检查时间戳是否有限
SELECT isfinite(timestamp 'infinity') AS is_timestamp_finite;
-- 结果: false

-- 检查间隔是否有限
SELECT isfinite(interval '4 hours') AS is_interval_finite;
-- 结果: true

-- 实际应用：数据验证
SELECT
    record_id,
    event_time,
    CASE
        WHEN isfinite(event_time) THEN 'Valid'
        ELSE 'Invalid - Infinite value'
    END AS validation_status
FROM event_log
WHERE created_date >= CURRENT_DATE - interval '1 day';

实际业务应用案例

案例 1：电商订单系统的时间处理

-- 创建订单表结构示例
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    estimated_delivery_days INTEGER DEFAULT 3,
    order_status VARCHAR(20) DEFAULT 'pending'
);

-- 插入示例数据
INSERT INTO orders (customer_id, order_date, estimated_delivery_days)
VALUES
    (1001, '2024-06-01 10:30:00+08', 3),
    (1002, '2024-06-02 14:15:00+08', 5),
    (1003, '2024-06-03 09:45:00+08', 2);

-- 查询订单的预计到达时间和延迟情况
SELECT
    order_id,
    customer_id,
    order_date,
    order_date + make_interval(days => estimated_delivery_days) AS estimated_delivery_time,
    CURRENT_TIMESTAMP - order_date AS time_since_order,
    CASE
        WHEN CURRENT_TIMESTAMP > order_date + make_interval(days => estimated_delivery_days)
        THEN 'Overdue'
        ELSE 'On Track'
    END AS delivery_status
FROM orders
WHERE order_status != 'delivered'
ORDER BY order_date;

案例 2：员工考勤系统

-- 员工打卡记录表
CREATE TABLE employee_attendance (
    record_id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    check_in_time TIMESTAMP,
    check_out_time TIMESTAMP,
    work_date DATE DEFAULT CURRENT_DATE
);

-- 计算每日工作时长和加班时间
SELECT
    employee_id,
    work_date,
    check_in_time,
    check_out_time,
    check_out_time - check_in_time AS total_work_duration,
    GREATEST(
        (check_out_time - check_in_time) - interval '8 hours',
        interval '0'
    ) AS overtime_duration,
    justify_hours(check_out_time - check_in_time) AS formatted_work_time
FROM employee_attendance
WHERE work_date >= CURRENT_DATE - interval '7 days'
    AND check_out_time IS NOT NULL
ORDER BY employee_id, work_date;

案例 3：数据分析的时间分组

-- 网站访问日志分析
WITH hourly_stats AS (
    SELECT
        date_trunc('hour', access_time) AS hour_bucket,
        count(*) AS page_views,
        count(DISTINCT user_id) AS unique_visitors
    FROM website_access_log
    WHERE access_time >= CURRENT_DATE - interval '30 days'
    GROUP BY date_trunc('hour', access_time)
),
daily_stats AS (
    SELECT
        date_trunc('day', access_time) AS day_bucket,
        count(*) AS daily_views,
        count(DISTINCT user_id) AS daily_unique_visitors
    FROM website_access_log
    WHERE access_time >= CURRENT_DATE - interval '30 days'
    GROUP BY date_trunc('day', access_time)
)
SELECT
    h.hour_bucket,
    h.page_views,
    h.unique_visitors,
    d.daily_views,
    d.daily_unique_visitors,
    round(h.page_views::numeric / d.daily_views * 100, 2) AS hourly_percentage
FROM hourly_stats h
JOIN daily_stats d ON date_trunc('day', h.hour_bucket) = d.day_bucket
ORDER BY h.hour_bucket;

性能优化建议

索引策略

-- 为日期/时间列创建适当的索引
CREATE INDEX idx_orders_order_date ON orders (order_date);
CREATE INDEX idx_events_date_trunc_day ON events (date_trunc('day', event_time));

-- 使用表达式索引加速时间截断查询
CREATE INDEX idx_sales_month ON sales (extract(year from sale_date), extract(month from sale_date));

查询优化

TIP

性能优化技巧

1. 使用索引友好的查询模式：避免在 WHERE 子句中对日期列进行函数操作
2. 预计算常用的时间值：如月初、月末等
3. 合理使用时区转换：避免不必要的时区转换操作
4. 批量处理时间计算：使用 CTE 或子查询预计算时间值

-- 不推荐：在 WHERE 子句中使用函数
SELECT * FROM orders
WHERE extract(year from order_date) = 2024;

-- 推荐：使用范围查询
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
    AND order_date < '2025-01-01';

OVERLAPS 操作符

OVERLAPS 操作符用于检查两个时间段是否有重叠：

-- 检查时间段重叠（使用开始和结束时间）
SELECT (TIME '2:00', TIME '4:00') OVERLAPS (TIME '3:00', TIME '5:00') AS has_overlap;
-- 结果: true

-- 检查时间段重叠（使用开始时间和持续时间）
SELECT (TIME '2:00', INTERVAL '2 hours') OVERLAPS (TIME '3:00', INTERVAL '1 hour') AS has_overlap;
-- 结果: true

-- 实际应用：会议室预订冲突检查
SELECT
    r1.room_id,
    r1.booking_id AS booking1,
    r2.booking_id AS booking2,
    r1.start_time,
    r1.end_time,
    r2.start_time,
    r2.end_time
FROM room_bookings r1
JOIN room_bookings r2 ON r1.room_id = r2.room_id
    AND r1.booking_id != r2.booking_id
WHERE (r1.start_time, r1.end_time) OVERLAPS (r2.start_time, r2.end_time)
    AND r1.booking_date = CURRENT_DATE;

时间计算流程图

总结

PostgreSQL 的日期/时间函数和操作符提供了强大而灵活的时间处理能力。通过合理使用这些函数，可以：

1. 精确处理各种时间计算需求：从简单的日期算术到复杂的时区转换
2. 优化数据分析性能：使用适当的时间分组和截断函数
3. 确保数据一致性：利用事务级时间函数保证时间戳的一致性
4. 简化业务逻辑：通过内置函数减少应用层的时间处理代码

WARNING

注意事项

• 处理时区时要特别注意 DST（夏令时）的影响
• 在高并发环境中，建议使用事务级时间函数确保一致性
• 对于性能敏感的查询，避免在 WHERE 子句中使用时间函数
• 定期检查和维护与时间相关的索引

掌握这些日期/时间函数的使用方法，将大大提升您在处理时间相关业务逻辑时的效率和准确性。