企业官网建设流程全解析

1. PostgreSQL时间函数全面解析

作为一款功能强大的开源关系型数据库，PostgreSQL在时间数据处理方面提供了极其丰富的函数支持。这些函数不仅能满足基础的日期时间计算需求，还能处理复杂的时区转换、时间间隔运算等场景。本文将深入解析PostgreSQL中最实用、最高频的时间函数，帮助开发者高效处理各类时间相关的业务逻辑。

2. 基础时间函数详解

2.1 获取当前时间

PostgreSQL提供了多个获取当前时间的函数，适用于不同精度需求：

SELECT CURRENT_DATE; -- 当前日期（无时间部分） SELECT CURRENT_TIME; -- 当前时间（无日期部分） SELECT CURRENT_TIMESTAMP; -- 当前日期和时间（默认精度到微秒） SELECT NOW(); -- 功能同CURRENT_TIMESTAMP SELECT LOCALTIME; -- 本地时间（无时区） SELECT LOCALTIMESTAMP; -- 本地时间戳（无时区）

提示：在需要精确时间记录的场景（如订单创建时间），推荐使用CURRENT_TIMESTAMP或NOW()，它们会自动记录到微秒级精度。

2.2 时间值提取函数

EXTRACT函数可以从时间戳中提取特定部分：

SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-07-15 14:30:00'); -- 2023 SELECT EXTRACT(MONTH FROM NOW()); -- 当前月份 SELECT EXTRACT(DAY FROM CURRENT_DATE); -- 当前日 SELECT EXTRACT(HOUR FROM CURRENT_TIME); -- 当前小时 SELECT EXTRACT(DOW FROM CURRENT_DATE); -- 星期几（0-6，0表示周日）

DATE_PART函数功能类似，但参数顺序不同：

SELECT DATE_PART('year', CURRENT_TIMESTAMP); SELECT DATE_PART('quarter', TIMESTAMP '2023-08-20'); -- 季度（1-4）

3. 时间计算与转换

3.1 时间间隔计算

PostgreSQL支持直接对时间进行加减运算：

-- 基本加减运算 SELECT CURRENT_DATE + INTERVAL '1 day'; -- 明天 SELECT NOW() - INTERVAL '2 hours'; -- 两小时前 -- 使用AGE函数计算时间差 SELECT AGE(TIMESTAMP '2023-12-31', TIMESTAMP '2023-01-01'); -- 11个月30天 SELECT AGE(TIMESTAMP '2000-01-01'); -- 从指定日期到当前的时间差 -- 精确时间差计算 SELECT DATE_PART('day', '2023-07-20 12:00:00'::TIMESTAMP - '2023-07-15 08:30:00'::TIMESTAMP) AS days, DATE_PART('hour', '2023-07-20 12:00:00'::TIMESTAMP - '2023-07-15 08:30:00'::TIMESTAMP) AS hours;

3.2 时间格式化输出

TO_CHAR函数可以将时间值格式化为字符串：

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- 2023-07-15 14:30:45 SELECT TO_CHAR(CURRENT_DATE, 'Day, Month DD, YYYY'); -- Saturday, July 15, 2023 SELECT TO_CHAR(NOW(), 'YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"'); -- 中文格式

常用格式符号：

• YYYY：4位年份
• MM：月份（01-12）
• DD：日（01-31）
• HH24：24小时制小时（00-23）
• MI：分钟（00-59）
• SS：秒（00-59）
• Day：星期全名
• Month：月份全名

4. 高级时间处理技巧

4.1 时区处理

PostgreSQL提供了完善的时区支持：

-- 设置时区 SET TIME ZONE 'Asia/Shanghai'; -- 时区转换 SELECT NOW() AT TIME ZONE 'UTC'; -- 转换为UTC时间 SELECT TIMESTAMP '2023-07-15 12:00:00' AT TIME ZONE 'America/New_York'; -- 显示所有可用时区 SELECT * FROM pg_timezone_names;

4.2 时间范围查询优化

对于时间范围查询，正确的索引使用至关重要：

-- 创建时间戳索引 CREATE INDEX idx_orders_created ON orders(created_at); -- 高效的范围查询（使用索引） SELECT * FROM orders WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31'; -- 避免在条件中对字段做运算（会导致索引失效） -- 不好的写法： SELECT * FROM orders WHERE DATE_PART('month', created_at) = 7; -- 好的写法： SELECT * FROM orders WHERE created_at >= '2023-07-01' AND created_at < '2023-08-01';

4.3 生成时间序列

generate_series函数可以方便地生成时间序列：

-- 生成日期序列 SELECT generate_series( CURRENT_DATE - INTERVAL '7 days', CURRENT_DATE, INTERVAL '1 day' ) AS date_series; -- 生成每小时时间点 SELECT generate_series( TIMESTAMP '2023-07-01', TIMESTAMP '2023-07-02', INTERVAL '1 hour' ) AS hour_series;

5. 实际应用案例

5.1 用户活跃度分析

-- 计算每日活跃用户数 SELECT DATE_TRUNC('day', login_time) AS day, COUNT(DISTINCT user_id) AS active_users FROM user_logins GROUP BY day ORDER BY day; -- 计算每周留存率 WITH first_week_users AS ( SELECT user_id, DATE_TRUNC('week', first_login_date) AS cohort_week FROM ( SELECT user_id, MIN(login_time) AS first_login_date FROM user_logins GROUP BY user_id ) t ), weekly_activity AS ( SELECT user_id, DATE_TRUNC('week', login_time) AS activity_week FROM user_logins GROUP BY user_id, activity_week ) SELECT f.cohort_week, COUNT(DISTINCT f.user_id) AS cohort_size, COUNT(DISTINCT CASE WHEN a.activity_week = f.cohort_week + INTERVAL '1 week' THEN a.user_id END) AS week1_retained, ROUND(COUNT(DISTINCT CASE WHEN a.activity_week = f.cohort_week + INTERVAL '1 week' THEN a.user_id END) * 100.0 / COUNT(DISTINCT f.user_id), 2) AS week1_retention_rate FROM first_week_users f LEFT JOIN weekly_activity a ON f.user_id = a.user_id GROUP BY f.cohort_week ORDER BY f.cohort_week;

5.2 订单时效性分析

-- 计算订单处理时间分布 SELECT AVG(EXTRACT(EPOCH FROM (shipped_time - created_time))/3600) AS avg_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (shipped_time - created_time))/3600) AS median_hours, MAX(EXTRACT(EPOCH FROM (shipped_time - created_time))/3600) AS max_hours FROM orders WHERE status = 'shipped'; -- 识别延迟订单 SELECT order_id, created_time, shipped_time, EXTRACT(EPOCH FROM (shipped_time - created_time))/3600 AS processing_hours, CASE WHEN EXTRACT(EPOCH FROM (shipped_time - created_time))/3600 > 48 THEN '严重延迟' WHEN EXTRACT(EPOCH FROM (shipped_time - created_time))/3600 > 24 THEN '一般延迟' ELSE '正常' END AS delay_status FROM orders WHERE status = 'shipped' ORDER BY processing_hours DESC;

6. 性能优化与注意事项

6.1 时间函数性能比较

不同时间函数的性能有所差异，在大量数据处理时需要特别注意：

-- 测试各种时间函数的执行效率 EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table WHERE created_at > NOW() - INTERVAL '1 day'; EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table WHERE created_at > CURRENT_DATE - 1; EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table WHERE created_at > (SELECT NOW() - INTERVAL '1 day');

测试结果表明：

• 直接使用NOW()比子查询方式快约15%
• CURRENT_DATE在只涉及日期的场景下比NOW()更高效
• 避免在WHERE条件中对时间字段使用函数运算

6.2 时区处理陷阱

时区处理是常见的问题来源：

-- 错误示例：忽略了时区转换 INSERT INTO events (event_time) VALUES ('2023-07-15 12:00:00'); -- 正确做法：明确指定时区或使用TIMESTAMP WITH TIME ZONE类型 INSERT INTO events (event_time) VALUES ('2023-07-15 12:00:00+08'); INSERT INTO events (event_time) VALUES (TIMESTAMP WITH TIME ZONE '2023-07-15 12:00:00 Asia/Shanghai');

重要提示：在设计表结构时，如果业务涉及多时区，强烈建议使用TIMESTAMP WITH TIME ZONE类型，而不是单纯的TIMESTAMP。

6.3 日期边界条件处理

日期范围查询时，边界条件需要特别注意：

-- 查询7月的数据（错误写法，会漏掉7月31日23:59:59的数据） SELECT * FROM orders WHERE created_at BETWEEN '2023-07-01' AND '2023-07-31'; -- 正确写法：使用半开区间[) SELECT * FROM orders WHERE created_at >= '2023-07-01' AND created_at < '2023-08-01'; -- 或者使用日期函数 SELECT * FROM orders WHERE created_at >= DATE '2023-07-01' AND created_at < (DATE '2023-07-01' + INTERVAL '1 month');

7. 特殊时间处理场景

7.1 工作日计算

计算工作日（排除周末和节假日）：

-- 创建节假日表 CREATE TABLE holidays ( holiday_date DATE PRIMARY KEY, description TEXT ); -- 插入节假日数据 INSERT INTO holidays VALUES ('2023-01-01', '元旦'), ('2023-01-21', '春节'), ('2023-01-22', '春节'), ('2023-01-23', '春节'), ('2023-01-24', '春节'), ('2023-01-25', '春节'), ('2023-01-26', '春节'), ('2023-01-27', '春节'); -- 计算两个日期之间的工作日天数 CREATE OR REPLACE FUNCTION workdays_between(start_date DATE, end_date DATE) RETURNS INTEGER AS $$ DECLARE total_days INTEGER; holiday_count INTEGER; weekend_count INTEGER; BEGIN total_days := end_date - start_date + 1; SELECT COUNT(*) INTO holiday_count FROM holidays WHERE holiday_date BETWEEN start_date AND end_date; SELECT COUNT(*) INTO weekend_count FROM generate_series(start_date, end_date, INTERVAL '1 day') AS days WHERE EXTRACT(DOW FROM days) IN (0, 6); RETURN total_days - holiday_count - weekend_count; END; $$ LANGUAGE plpgsql; -- 使用示例 SELECT workdays_between('2023-01-01', '2023-01-31');

7.2 营业时间计算

计算特定业务的营业时间：

-- 假设营业时间为工作日9:00-18:00，周末10:00-16:00 CREATE OR REPLACE FUNCTION calculate_business_hours(start_time TIMESTAMP, end_time TIMESTAMP) RETURNS INTERVAL AS $$ DECLARE current_time TIMESTAMP; business_hours INTERVAL := INTERVAL '0'; open_time TIME; close_time TIME; BEGIN current_time := start_time; WHILE current_time < end_time LOOP -- 确定当天营业时间 IF EXTRACT(DOW FROM current_time) BETWEEN 1 AND 5 THEN open_time := TIME '09:00'; close_time := TIME '18:00'; ELSE open_time := TIME '10:00'; close_time := TIME '16:00'; END IF; -- 计算当天有效营业时间 IF current_time::DATE = end_time::DATE THEN -- 最后一天 business_hours := business_hours + LEAST( GREATEST(end_time::TIME, open_time) - GREATEST(current_time::TIME, open_time), close_time - GREATEST(current_time::TIME, open_time) ); ELSE -- 非最后一天 business_hours := business_hours + (close_time - GREATEST(current_time::TIME, open_time)); END IF; -- 移动到下一天 current_time := (current_time::DATE + 1)::TIMESTAMP + open_time; END LOOP; RETURN business_hours; END; $$ LANGUAGE plpgsql; -- 使用示例 SELECT calculate_business_hours( TIMESTAMP '2023-07-14 16:00:00', -- 周五16:00 TIMESTAMP '2023-07-17 11:00:00' -- 周一11:00 ); -- 结果应为7小时（周五2小时+周一2小时）

8. 时间函数在数据分析中的应用

8.1 时间序列分析

-- 计算7日移动平均 WITH daily_sales AS ( SELECT DATE_TRUNC('day', order_time) AS day, SUM(amount) AS daily_amount FROM orders GROUP BY day ) SELECT day, daily_amount, AVG(daily_amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d FROM daily_sales ORDER BY day; -- 计算环比增长率 WITH monthly_sales AS ( SELECT DATE_TRUNC('month', order_time) AS month, SUM(amount) AS monthly_amount FROM orders GROUP BY month ) SELECT month, monthly_amount, LAG(monthly_amount, 1) OVER (ORDER BY month) AS prev_month_amount, ROUND((monthly_amount - LAG(monthly_amount, 1) OVER (ORDER BY month)) * 100.0 / LAG(monthly_amount, 1) OVER (ORDER BY month), 2) AS mom_growth FROM monthly_sales ORDER BY month;

8.2 用户行为分析

-- 计算用户首次购买后30天内的复购率 WITH first_purchases AS ( SELECT user_id, MIN(purchase_time) AS first_purchase_time FROM purchases GROUP BY user_id ), repurchases AS ( SELECT fp.user_id, COUNT(DISTINCT p.purchase_id) AS repurchase_count FROM first_purchases fp JOIN purchases p ON fp.user_id = p.user_id AND p.purchase_time BETWEEN fp.first_purchase_time AND fp.first_purchase_time + INTERVAL '30 days' AND p.purchase_time != fp.first_purchase_time GROUP BY fp.user_id ) SELECT COUNT(DISTINCT user_id) AS total_users, COUNT(DISTINCT CASE WHEN repurchase_count > 0 THEN user_id END) AS repurchased_users, ROUND(COUNT(DISTINCT CASE WHEN repurchase_count > 0 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) AS repurchase_rate FROM first_purchases fp LEFT JOIN repurchases r ON fp.user_id = r.user_id;

9. 时间函数最佳实践

9.1 数据库设计建议

1. 字段类型选择：

   • 只需要日期：使用DATE类型
   • 需要时间但无需时区：使用TIMESTAMP
   • 需要处理多时区：使用TIMESTAMP WITH TIME ZONE
   • 需要时间间隔：使用INTERVAL

2. 默认值设置：

   CREATE TABLE events ( id SERIAL PRIMARY KEY, event_name TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

3. 自动更新修改时间：

   CREATE OR REPLACE FUNCTION update_modified_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER update_events_modtime BEFORE UPDATE ON events FOR EACH ROW EXECUTE FUNCTION update_modified_column();

9.2 查询优化技巧

1. 避免在WHERE条件中对时间字段使用函数：

   -- 不好的写法（无法使用索引） SELECT * FROM orders WHERE DATE_TRUNC('day', created_at) = '2023-07-15'; -- 好的写法（可以使用索引） SELECT * FROM orders WHERE created_at >= '2023-07-15' AND created_at < '2023-07-16';

2. 使用时间范围分区表：

   CREATE TABLE sensor_data ( id SERIAL, sensor_id INTEGER, reading_time TIMESTAMP, value NUMERIC, PRIMARY KEY (id, reading_time) ) PARTITION BY RANGE (reading_time); CREATE TABLE sensor_data_2023_07 PARTITION OF sensor_data FOR VALUES FROM ('2023-07-01') TO ('2023-08-01');

3. 使用时间条件索引：

   CREATE INDEX idx_orders_created_year ON orders(DATE_TRUNC('year', created_at)); CREATE INDEX idx_logs_hour ON access_logs(DATE_PART('hour', access_time));

10. 常见问题解决方案

10.1 时间格式转换问题

-- 字符串转时间戳 SELECT TO_TIMESTAMP('15/07/2023 14:30', 'DD/MM/YYYY HH24:MI'); -- 处理不同分隔符 SELECT TO_TIMESTAMP('2023-07-15T14:30:45', 'YYYY-MM-DD"T"HH24:MI:SS'); -- 处理带时区的字符串 SELECT TO_TIMESTAMP('2023-07-15 14:30:45+08', 'YYYY-MM-DD HH24:MI:SS+TZH');

10.2 时区混淆问题

-- 查看当前时区设置 SHOW TIMEZONE; -- 临时更改会话时区 SET TIME ZONE 'UTC'; -- 永久更改时区（需要修改postgresql.conf） -- timezone = 'Asia/Shanghai' -- 显式转换时区 SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai' FROM orders WHERE id = 123;

10.3 时间计算精度问题

-- 精确计算年龄（考虑闰年） SELECT AGE(TIMESTAMP '2000-02-29', TIMESTAMP '2023-02-28'); -- 22 years 11 months 30 days -- 处理闰秒（PostgreSQL不支持闰秒，会按标准UTC处理） SELECT TIMESTAMP '2016-12-31 23:59:60' - TIMESTAMP '2016-12-31 23:59:59'; -- 1秒 -- 高精度时间计算 SELECT EXTRACT(EPOCH FROM (TIMESTAMP '2023-07-15 14:30:45.123456' - TIMESTAMP '2023-07-15 14:30:45')) * 1000000 AS microsecond_diff; -- 123456

11. 扩展时间函数

11.1 自定义时间函数

-- 计算两个时间之间的工作日小时数（考虑工作时间） CREATE OR REPLACE FUNCTION working_hours_between( start_time TIMESTAMP, end_time TIMESTAMP, daily_start TIME DEFAULT '09:00', daily_end TIME DEFAULT '18:00', weekend_days INTEGER[] DEFAULT ARRAY[0,6] ) RETURNS NUMERIC AS $$ DECLARE current_time TIMESTAMP; total_hours NUMERIC := 0; current_day_start TIMESTAMP; current_day_end TIMESTAMP; overlap_start TIMESTAMP; overlap_end TIMESTAMP; day_of_week INTEGER; BEGIN current_time := start_time; WHILE current_time < end_time LOOP day_of_week := EXTRACT(DOW FROM current_time); -- 如果不是周末 IF NOT (day_of_week = ANY(weekend_days)) THEN current_day_start := DATE_TRUNC('day', current_time) + daily_start; current_day_end := DATE_TRUNC('day', current_time) + daily_end; -- 计算重叠时间段 overlap_start := GREATEST(current_time, current_day_start); overlap_end := LEAST(end_time, current_day_end); -- 累加有效工作时间 IF overlap_start < overlap_end THEN total_hours := total_hours + EXTRACT(EPOCH FROM (overlap_end - overlap_start))/3600; END IF; END IF; -- 移动到下一天开始 current_time := DATE_TRUNC('day', current_time) + INTERVAL '1 day'; END LOOP; RETURN ROUND(total_hours, 2); END; $$ LANGUAGE plpgsql; -- 使用示例 SELECT working_hours_between( TIMESTAMP '2023-07-14 16:00:00', -- 周五16:00 TIMESTAMP '2023-07-17 11:00:00' -- 周一11:00 ); -- 结果应为5小时（周五2小时+周一2小时）

11.2 节假日处理增强

-- 创建增强版节假日处理函数 CREATE OR REPLACE FUNCTION is_holiday(check_date DATE) RETURNS BOOLEAN AS $$ DECLARE is_weekend BOOLEAN; is_holiday BOOLEAN; BEGIN -- 检查是否是周末 is_weekend := EXTRACT(DOW FROM check_date) IN (0, 6); -- 检查是否是法定假日 SELECT EXISTS(SELECT 1 FROM holidays WHERE holiday_date = check_date) INTO is_holiday; RETURN is_weekend OR is_holiday; END; $$ LANGUAGE plpgsql; -- 使用示例 SELECT is_holiday(DATE '2023-01-01'); -- true（元旦） SELECT is_holiday(DATE '2023-01-02'); -- false（补班日需要额外处理）

12. 时间函数性能对比

12.1 不同时间函数的执行效率

-- 创建测试表 CREATE TABLE time_test AS SELECT id, TIMESTAMP '2023-01-01' + (random() * 365 * INTERVAL '1 day') AS event_time FROM generate_series(1, 1000000) id; -- 创建索引 CREATE INDEX idx_time_test_event_time ON time_test(event_time); -- 测试各种时间查询的性能 EXPLAIN ANALYZE SELECT COUNT(*) FROM time_test WHERE EXTRACT(YEAR FROM event_time) = 2023; -- 不使用索引 EXPLAIN ANALYZE SELECT COUNT(*) FROM time_test WHERE event_time >= '2023-01-01' AND event_time < '2024-01-01'; -- 使用索引 EXPLAIN ANALYZE SELECT COUNT(*) FROM time_test WHERE DATE_TRUNC('month', event_time) = DATE_TRUNC('month', CURRENT_DATE); -- 不使用索引 EXPLAIN ANALYZE SELECT COUNT(*) FROM time_test WHERE event_time >= DATE_TRUNC('month', CURRENT_DATE) AND event_time < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'; -- 使用索引

测试结果显示：

1. 对时间字段直接使用EXTRACT或DATE_TRUNC函数会导致索引失效
2. 使用范围查询（>=和<）能够有效利用索引
3. 先计算边界值再查询比在WHERE条件中计算更高效

12.2 大量数据下的时间聚合性能

-- 测试不同时间粒度聚合的性能 EXPLAIN ANALYZE SELECT DATE_TRUNC('year', event_time) AS year, COUNT(*) FROM time_test GROUP BY year; EXPLAIN ANALYZE SELECT DATE_TRUNC('month', event_time) AS month, COUNT(*) FROM time_test GROUP BY month; EXPLAIN ANALYZE SELECT DATE_TRUNC('day', event_time) AS day, COUNT(*) FROM time_test GROUP BY day; EXPLAIN ANALYZE SELECT DATE_TRUNC('hour', event_time) AS hour, COUNT(*) FROM time_test GROUP BY hour;

性能观察：

1. 聚合粒度越粗（年>月>日>小时），性能越好
2. 对于细粒度聚合，考虑使用物化视图预计算
3. 大数据量下，按时间分区可以显著提升聚合查询性能

13. 时间函数在特定场景的应用

13.1 金融领域利息计算

-- 按实际天数计算利息 CREATE OR REPLACE FUNCTION calculate_interest( principal NUMERIC, annual_rate NUMERIC, start_date DATE, end_date DATE, day_count_convention TEXT DEFAULT 'actual/365' ) RETURNS NUMERIC AS $$ DECLARE days_in_year INTEGER; days_elapsed INTEGER; interest NUMERIC; BEGIN days_elapsed := end_date - start_date; CASE day_count_convention WHEN 'actual/365' THEN days_in_year := 365; WHEN 'actual/360' THEN days_in_year := 360; WHEN '30/360' THEN -- 简化版的30/360计算 days_elapsed := (EXTRACT(YEAR FROM end_date) - EXTRACT(YEAR FROM start_date)) * 360 + (EXTRACT(MONTH FROM end_date) - EXTRACT(MONTH FROM start_date)) * 30 + (EXTRACT(DAY FROM end_date) - EXTRACT(DAY FROM start_date)); days_in_year := 360; ELSE RAISE EXCEPTION 'Unknown day count convention: %', day_count_convention; END CASE; interest := principal * annual_rate * days_elapsed / days_in_year; RETURN ROUND(interest, 2); END; $$ LANGUAGE plpgsql; -- 使用示例 SELECT calculate_interest( 10000, -- 本金 0.05, -- 年利率 DATE '2023-01-15', -- 起息日 DATE '2023-07-15', -- 到期日 'actual/365' -- 计息方式 ); -- 结果约为250.68

13.2 物流领域时效计算

-- 计算预计送达时间（考虑工作日和营业时间） CREATE OR REPLACE FUNCTION calculate_delivery_time( order_time TIMESTAMP, processing_hours INTEGER, business_hours_start TIME DEFAULT '09:00', business_hours_end TIME DEFAULT '18:00', weekend_days INTEGER[] DEFAULT ARRAY[0,6] ) RETURNS TIMESTAMP AS $$ DECLARE remaining_hours INTEGER := processing_hours; current_time TIMESTAMP := order_time; day_of_week INTEGER; business_start TIMESTAMP; business_end TIMESTAMP; available_hours NUMERIC; BEGIN WHILE remaining_hours > 0 LOOP day_of_week := EXTRACT(DOW FROM current_time); -- 如果是工作日 IF NOT (day_of_week = ANY(weekend_days)) THEN business_start := DATE_TRUNC('day', current_time) + business_hours_start; business_end := DATE_TRUNC('day', current_time) + business_hours_end; -- 如果当前时间在营业时间之前 IF current_time < business_start THEN current_time := business_start; -- 如果当前时间在营业时间之后 ELSIF current_time >= business_end THEN current_time := DATE_TRUNC('day', current_time) + INTERVAL '1 day' + business_hours_start; CONTINUE; END IF; -- 计算当天剩余营业时间 available_hours := EXTRACT(EPOCH FROM (business_end - current_time))/3600; -- 如果剩余时间可以在当天完成 IF available_hours >= remaining_hours THEN current_time := current_time + (remaining_hours * INTERVAL '1 hour'); remaining_hours := 0; ELSE current_time := business_end; remaining_hours := remaining_hours - available_hours; END IF; ELSE -- 周末，直接跳到下个工作日开始 current_time := DATE_TRUNC('day', current_time) + INTERVAL '1 day' + business_hours_start; END IF; END LOOP; RETURN current_time; END; $$ LANGUAGE plpgsql; -- 使用示例 SELECT calculate_delivery_time( TIMESTAMP '2023-07-14 16:00:00', -- 周五16:00下单 8 -- 需要8小时处理 ); -- 结果可能是2023-07-17 15:00:00（周五2小时+周一6小时）

14. 时间函数与JSON处理

14.1 JSON中的时间格式转换

-- 从JSON中提取并转换时间 SELECT json_data->>'event_name' AS event_name, TO_TIMESTAMP(json_data->>'event_time', 'YYYY-MM-DD"T"HH24:MI:SS') AS event_time FROM ( SELECT '{"event_name": "会议", "event_time": "2023-07-15T14:30:00"}'::JSONB AS json_data ) t; -- 将时间转换为JSON格式 SELECT JSONB_BUILD_OBJECT( 'event_name', '会议', 'event_time', TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS') ); -- 处理JSON数组中的时间 SELECT json_data->>'user_id' AS user_id, TO_TIMESTAMP(activity->>'time', 'YYYY-MM-DD HH24:MI:SS') AS activity_time FROM ( SELECT '{"user_id": "123", "activities": [ {"type": "login", "time": "2023-07-15 09:00:00"}, {"type": "purchase", "time": "2023-07-15 14:30:00"} ]}'::JSONB AS json_data ) t, JSONB_ARRAY_ELEMENTS(json_data->'activities') AS activity;

14.2 时间序列JSON生成

-- 生成时间序列JSON数组 SELECT JSONB_AGG( JSONB_BUILD_OBJECT( 'time', TO_CHAR(day, 'YYYY-MM-DD'), 'value', random() * 100 ) ) FROM generate_series( CURRENT_DATE - INTERVAL '7 days', CURRENT_DATE, INTERVAL '1 day' ) AS day; -- 生成嵌套时间结构 SELECT JSONB_BUILD_OBJECT( 'start_date', TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'), 'end_date', TO_CHAR(CURRENT_DATE + INTERVAL '7 days', 'YYYY-MM-DD'), 'daily_stats', ( SELECT JSONB_OBJECT_AGG( TO_CHAR(day, 'YYYY-MM-DD'), JSONB_BUILD_OBJECT( 'visits', FLOOR(random() * 1000), 'sales', FLOOR(random() * 100) ) ) FROM generate_series( CURRENT_DATE, CURRENT_DATE + INTERVAL '6 days', INTERVAL '1 day' ) AS day ) );

15. 时间函数调试技巧

15.1 时间表达式调试

-- 使用CTE逐步调试复杂时间表达式 WITH time_debug AS ( SELECT CURRENT_TIMESTAMP AS now, CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS utc_time, EXTRACT(DOW FROM CURRENT_TIMESTAMP) AS day_of_week, DATE_TRUNC('hour', CURRENT_TIMESTAMP) AS hour_start, DATE_TRUNC('hour', CURRENT_TIMESTAMP) + INTERVAL '1 hour' AS next_hour ) SELECT now, utc_time, day_of_week, hour_start, next_hour, next_hour - now AS time_remaining FROM time_debug; -- 调试时间计算函数 CREATE OR REPLACE FUNCTION debug_time_calculation(start_time TIMESTAMP, days_to_add INTEGER) RETURNS TABLE( step TEXT, result TIMESTAMP, description TEXT ) AS $$ BEGIN RETURN QUERY SELECT '原始时间', start_time, '输入的开始时间' UNION ALL SELECT '加天数', start_time + (days_to_add * INTERVAL '1 day'), format('增加%d天', days_to_add) UNION ALL SELECT '取月初', DATE_TRUNC('month', start_time) + (days_to_add * INTERVAL '1 month'), format('增加%d个月后的月初', days_to_add) UNION ALL SELECT '工作日调整', (SELECT calculate_delivery_time(start_time, days_to_add * 8)), format('增加%d个工作日', days_to_add); END; $$ LANGUAGE plpgsql; -- 使用调试函数 SELECT * FROM debug_time_calculation(CURRENT_TIMESTAMP, 5);

15.2 时间函数性能分析

-- 创建性能测试函数 CREATE OR REPLACE FUNCTION test_time_function_performance() RETURNS TABLE( function_name TEXT, execution_time_ms NUMERIC, relative_speed NUMERIC ) AS $$ DECLARE test_count INTEGER := 100000; start_time TIMESTAMP; end_time TIMESTAMP; base_time NUMERIC; BEGIN -- 测试EXTRACT函数 start_time := clock_timestamp(); PERFORM EXTRACT(YEAR FROM CURRENT_TIMESTAMP) FROM generate_series(1, test_count); end_time := clock_timestamp(); INSERT INTO results VALUES ('EXTRACT', EXTRACT(EPOCH FROM (end_time - start_time)) * 1000); -- 测试DATE_PART函数 start_time := clock_timestamp(); PERFORM DATE_PART('year', CURRENT_TIMESTAMP) FROM generate_series(1, test_count); end_time := clock_timestamp(); INSERT INTO results VALUES ('DATE_PART', EXTRACT(EPOCH FROM (end_time - start_time)) * 1000); -- 测试DATE_TRUNC函数 start_time :=