SELECT
EXTRACT(YEAR FROM NOW()) AS year,
EXTRACT(MONTH FROM NOW()) AS month,
EXTRACT(DAY FROM NOW()) AS day,
EXTRACT(HOUR FROM NOW()) AS hour,
EXTRACT(DOW FROM NOW()) AS weekday, -- 0=周日
EXTRACT(DOY FROM NOW()) AS yearday;
-- 等价
SELECT DATE_PART('hour', NOW());
常用字段
字段
说明
YEAR, MONTH, DAY
年月日
HOUR, MINUTE, SECOND
时分秒
DOW
星期几（0=周日）
DOY
一年中的第几天
EPOCH
秒级 Unix 时间戳
CENTURY, DECADE, QUARTER
世纪、十年、季度
SELECT EXTRACT(EPOCH FROM NOW()); -- 1743944625.123456
SELECT AGE(birthday) FROM users; -- 当前年龄
SELECT AGE('2025-12-31'::DATE); -- 距离年底
七、时区处理（关键！）
PostgreSQL 默认存储 UTC，显示时自动转换。
-- 设置会话时区
SET TIME ZONE 'Asia/Shanghai';
SET TIME ZONE 'UTC';
SET TIME ZONE 'America/New_York';
-- 查看当前时区
SHOW TIMEZONE;
-- 转换时区
SELECT NOW() AT TIME ZONE 'UTC'; -- 转成 UTC
SELECT '2025-04-05 10:00:00+08'::TIMESTAMPTZ AT TIME ZONE 'UTC';
最佳实践
-- 存储：用 TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT NOW()
-- 查询：前端指定时区
SELECT created_at AT TIME ZONE 'Asia/Shanghai' FROM events;
八、间隔（INTERVAL）函数
-- 构造
SELECT INTERVAL '3 years 2 months';
SELECT 'P1Y2M3DT4H5M6S'::INTERVAL; -- ISO 8601 格式
-- 提取
SELECT JUSTIFY_DAYS(INTERVAL '35 days'); -- 1 mon 5 days
SELECT JUSTIFY_HOURS(INTERVAL '30 hours'); -- 1 day 6 hours
SELECT JUSTIFY_INTERVAL(INTERVAL '1 mon 40 days');
九、范围查询与索引优化
1. 推荐写法（Sargable）
-- 推荐：常量在右边
WHERE created_at >= '2025-04-01 00:00:00+08'
AND created_at < '2025-04-02 00:00:00+08'
-- 避免：函数包裹列
-- WHERE DATE(created_at) = '2025-04-01' -- 不能用索引
2. 索引
CREATE INDEX idx_orders_created_at ON orders(created_at);
3. 分区表（按月）
CREATE TABLE orders_202504 PARTITION OF orders
FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');
十、常见函数汇总表
类别
函数
示例
当前时间
NOW(), CURRENT_TIMESTAMP
NOW()
构造
MAKE_DATE, MAKE_TIMESTAMP
MAKE_DATE(2025,4,5)
提取
EXTRACT(), DATE_PART()
EXTRACT(MONTH FROM NOW())
格式化
TO_CHAR()
TO_CHAR(NOW(), 'YYYY-MM-DD')
解析
TO_TIMESTAMP()
TO_TIMESTAMP('2025/04/05', 'YYYY/MM/DD')
运算
+ INTERVAL, AGE()
NOW() + INTERVAL '1 day'
时区
AT TIME ZONE
NOW() AT TIME ZONE 'UTC'
间隔
JUSTIFY_INTERVAL()
JUSTIFY_DAYS(INTERVAL '35 days')
十一、实战示例
1. 统计最近 7 天活跃用户
SELECT DATE(created_at) AS day, COUNT(DISTINCT user_id)
FROM logins
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at)
ORDER BY day;
2. 查询本月第一天和最后一天
SELECT
DATE_TRUNC('month', NOW()) AS first_day,
(DATE_TRUNC('month', NOW()) + INTERVAL '1 month - 1 day')::DATE AS last_day;
3. 时间段重叠判断
-- 两个预约是否冲突
SELECT *
FROM bookings b1, bookings b2
WHERE b1.room_id = b2.room_id
AND b1.id <> b2.id
AND b1.start_time < b2.end_time
AND b1.end_time > b2.start_time;
4. 生成时间序列（PostgreSQL 8.4+）
SELECT GENERATE_SERIES('2025-04-01'::DATE, '2025-04-30'::DATE, INTERVAL '1 day') AS day;
十二、性能优化技巧
技巧
说明
用 TIMESTAMPTZ
自动处理时区
避免 DATE(col)
破坏索引
用 DATE_TRUNC 分组
高效
范围查询用 < 而非 BETWEEN
避免边界问题
建 BRIN 索引（大表）
CREATE INDEX ON logs USING BRIN(created_at);
十三、常见陷阱与避坑
错误
原因
解决
时区错乱
用 TIMESTAMP 存本地时间
改用 TIMESTAMPTZ
夏令时重复
2025-03-30 02:30 不存在
用 TIMESTAMPTZ
BETWEEN 包含边界
00:00:00 被重复算
改用 <
TO_CHAR 性能差
大量调用
缓存或前端格式化
INTERVAL '1 month' 不固定
2月加1月 → 3月
用 DATE_TRUNC
十四、最佳实践脚本
1. 标准时间列定义
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
name TEXT,
start_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
end_time TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CHECK (end_time > start_time)
);
-- 更新触发器
CREATE TRIGGER trg_events_updated_at
BEFORE UPDATE ON events
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
2. 按天分区 + 自动创建
CREATE OR REPLACE FUNCTION create_partition_if_not_exists(p_date DATE)
RETURNS VOID AS $$
DECLARE
partition_name TEXT := 'events_' || TO_CHAR(p_date, 'YYYYMMDD');
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_tables
WHERE tablename = partition_name
) THEN
EXECUTE format(
'CREATE TABLE %I PARTITION OF events
FOR VALUES FROM (%L) TO (%L)',
partition_name,
p_date,
p_date + INTERVAL '1 day'
);
END IF;
END;
$$ LANGUAGE plpgsql;
十五、速查表
操作
SQL
当前时间
NOW()
今天 0 点
CURRENT_DATE
本月第一天
DATE_TRUNC('month', NOW())
7 天前
NOW() - INTERVAL '7 days'
年龄
AGE(birthday)
格式化
TO_CHAR(NOW(), 'YYYY-MM-DD')
解析
TO_TIMESTAMP('2025/04/05', 'YYYY/MM/DD')
时区转换
ts AT TIME ZONE 'UTC'
十六、决策树
graph TD
A[需要存时间?] -->|是| B{是否跨时区?}
B -->|是| C[用 TIMESTAMPTZ]
B -->|否| D{是否需时区?}
D -->|是| E[用 TIMETZ]
D -->|否| F[用 TIMESTAMP]
A -->|否| G[用 DATE]