一、时间计算的三大核心场景
当你遇到这些需求时,本文就是你的救星🌟:
-
倒计时功能:计算活动剩余天数
-
用户行为分析:统计操作间隔时间
-
跨国系统:多时区时间统一管理
-
报表生成:自动计算同比/环比周期
二、时间计算的5种神兵利器
1. 减法直接计算时间差
-- 计算订单处理时长(小时)
SELECT order_id,(complete_time - create_time) AS 时间差,EXTRACT(HOUR FROM (complete_time - create_time)) AS 小时数
FROM orders;
输出示例:"1 day 02:30:00"
→ 26.5小时
2. AGE函数计算精准年龄
-- 计算员工精确工龄
SELECT name,AGE(now(), hire_date) AS 总工龄,EXTRACT(YEAR FROM AGE(now(), hire_date)) || '年' AS 年数
FROM employees;
输出结果:3 years 4 mons 5 days
→ 3年
3. 时间分量加减法
-- 计算三天后的日期
SELECT now() + INTERVAL '3 days';-- 精确时间计算(2小时15分钟后)
UPDATE meetings
SET start_time = start_time + INTERVAL '2 HOURS 15 MINUTES'
4. 日期截断函数DATE_TRUNC
-- 获取当月第一天
SELECT DATE_TRUNC('month', now());-- 按周统计数据
SELECT DATE_TRUNC('week', log_time) AS 周开始,COUNT(*)
FROM logs
GROUP BY 1;
5. 生成时间序列GENERATE_SERIES
-- 生成最近7天日期
SELECT generate_series(current_date - INTERVAL '6 days',current_date,INTERVAL '1 day'
) AS date;
三、跨国系统必备:时区转换全攻略
1. 查看数据库时区
SHOW TIME ZONE; -- 输出示例:Asia/Shanghai
2. 时区转换三种写法
-- 写法1:AT TIME ZONE关键字
SELECT now() AT TIME ZONE 'Asia/Tokyo';-- 写法2:带时区类型转换
SELECT '2024-01-20 15:00:00'::timestamp WITH TIME ZONE AT TIME ZONE 'UTC';-- 写法3:时区函数
SELECT timezone('America/New_York', now());
3. 全球时区对照案例
-- 纽约时间转北京时间
SELECT event_time AT TIME ZONE 'America/New_York' AS 纽约时间,event_time AT TIME ZONE 'Asia/Shanghai' AS 北京时间
FROM global_events;
四、实战演练:电商大促分析
/* 需求:分析双11每小时成交额变化趋势(UTC+8时区)*/
SELECTEXTRACT(HOUR FROM payment_time AT TIME ZONE 'Asia/Shanghai') AS 小时,SUM(amount) AS 成交额
FROM payments
WHERE payment_time >= '2023-11-11 00:00:00+08' ANDpayment_time <= '2023-11-12 00:00:00+08'
GROUP BY 1
ORDER BY 1;
五、扩展学习:常用时间函数表
函数 | 描述 | 示例 |
---|---|---|
NOW() | 当前时间 | SELECT NOW() |
CURRENT_DATE | 当前日期 | SELECT CURRENT_DATE |
MAKE_INTERVAL | 创建间隔 | MAKE_INTERVAL(days => 7) |
ISFINITE | 检查时间有效性 | WHERE ISFINITE(expire_time) |
觉得有用?点赞❤️收藏⭐️关注✅,系统学习PostgreSQL看👉#PostgreSQL从入门到精通 合集!