PostgreSQL教程FG043-PG日期时间类型：全类型详解与格式化操作

本文档详细介绍PostgreSQL日期时间类型的使用方法和格式化操作，风哥教程参考PostgreSQL官方文档内容，适合数据库管理员和开发人员在生产环境中使用这些类型进行时间数据的存储和查询。

Part01-基础概念与理论知识

1.1 PostgreSQL日期时间类型概述

PostgreSQL支持多种日期时间类型，用于存储不同精度和范围的时间数据。这些类型包括DATE、TIME、TIMESTAMP和INTERVAL，每种类型都有其特定的用途和存储方式。更多视频教程www.fgedu.net.cn

PostgreSQL日期时间类型：

DATE：存储日期，格式为YYYY-MM-DD，范围从4713 BC到5874897 AD
TIME：存储时间，格式为HH:MI:SS，范围从00:00:00到23:59:59.999999
TIMESTAMP：存储日期和时间，格式为YYYY-MM-DD HH:MI:SS，范围从4713 BC到5874897 AD
TIMESTAMPTZ：带时区的时间戳，存储UTC时间并包含时区信息
INTERVAL：存储时间间隔，格式为[quantity unit]，如1 day 2 hours

1.2 日期时间类型存储原理

PostgreSQL日期时间类型的存储方式：

DATE：使用4字节存储，存储从2000-01-01开始的天数偏移量
TIME：使用8字节存储，存储从午夜开始的微秒数
TIMESTAMP：使用8字节存储，存储从2000-01-01 00:00:00开始的微秒数
TIMESTAMPTZ：使用8字节存储，存储UTC时间的微秒数，时区信息由客户端设置
INTERVAL：使用16字节存储，包含月、日、微秒三个部分

1.3 日期时间精度

— 日期时间精度

— 日期时间类型可以指定精度，如TIME(3)表示精度为毫秒
CREATE TABLE fgedu_events (
id SERIAL PRIMARY KEY,
event_name VARCHAR(100),
event_date DATE,
event_time TIME(3),
event_timestamp TIMESTAMP(6),
event_timestamptz TIMESTAMPTZ(3),
event_duration INTERVAL
);

风哥提示：选择合适的日期时间类型和精度对于数据存储和查询性能非常重要。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 日期时间类型使用最佳实践

— 日期时间类型使用最佳实践

— 1. 选择合适的类型
— – 只需要日期：使用DATE
— – 只需要时间：使用TIME
— – 需要日期和时间：使用TIMESTAMP
— – 需要时区支持：使用TIMESTAMPTZ
— – 需要时间间隔：使用INTERVAL

— 2. 精度选择
— – 一般业务场景：TIMESTAMP(3)（毫秒精度）
— – 高精度场景：TIMESTAMP(6)（微秒精度）
— – 低精度场景：TIMESTAMP(0)（秒精度）

— 3. 时区处理
— – 统一使用UTC时间存储
— – 应用层处理时区转换
— – 避免在数据库中存储时区信息

2.2 时区设置建议

时区设置建议：

数据库时区：设置为UTC，确保数据存储的一致性
会话时区：根据用户所在地区设置
应用时区：在应用层面处理时区转换
避免混用：不要在同一系统中混用带时区和不带时区的时间戳

风哥教程针对生产环境建议：统一使用UTC时间存储，避免时区混乱。from PostgreSQL视频:www.itpux.com

2.3 日期时间字段索引优化

日期时间字段索引优化：

选择合适的索引类型：对于范围查询，使用B树索引
索引精度：根据查询精度创建索引，如只按天查询可使用日期截断函数
复合索引：对于多条件查询，创建复合索引
分区表：对于大量时间数据，使用分区表提高查询性能

Part03-生产环境项目实施方案

3.1 日期时间类型使用详解

3.1.1 日期时间类型定义

— 日期时间类型定义

— 创建包含日期时间类型的表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_id VARCHAR(20) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
order_time TIME NOT NULL,
order_timestamp TIMESTAMP NOT NULL,
order_timestamptz TIMESTAMPTZ NOT NULL,
delivery_time TIMESTAMP,
order_duration INTERVAL
);

3.1.2 日期时间数据插入

— 日期时间数据插入

— 插入日期时间数据
INSERT INTO fgedu_orders (
order_id, customer_id, order_date, order_time,
order_timestamp, order_timestamptz, delivery_time, order_duration
) VALUES (
‘ORD-20260407-001’, 101,
‘2026-04-07′, ’10:30:45’,
‘2026-04-07 10:30:45’,
‘2026-04-07 10:30:45+08’,
‘2026-04-07 14:30:00’,
‘4 hours’
);

— 使用当前时间
INSERT INTO fgedu_orders (
order_id, customer_id, order_date, order_time,
order_timestamp, order_timestamptz, delivery_time, order_duration
) VALUES (
‘ORD-20260407-002’, 102,
CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP + INTERVAL ‘3 hours’,
‘3 hours’
);

3.2 日期时间函数使用

3.2.1 常用日期时间函数

— 常用日期时间函数

— 获取当前时间
SELECT CURRENT_DATE; — 当前日期
SELECT CURRENT_TIME; — 当前时间
SELECT CURRENT_TIMESTAMP; — 当前时间戳
SELECT NOW(); — 当前时间戳（同CURRENT_TIMESTAMP）

— 日期时间提取
SELECT EXTRACT(YEAR FROM CURRENT_DATE); — 提取年份
SELECT EXTRACT(MONTH FROM CURRENT_DATE); — 提取月份
SELECT EXTRACT(DAY FROM CURRENT_DATE); — 提取日
SELECT EXTRACT(HOUR FROM CURRENT_TIME); — 提取小时
SELECT EXTRACT(MINUTE FROM CURRENT_TIME); — 提取分钟
SELECT EXTRACT(SECOND FROM CURRENT_TIME); — 提取秒

— 日期时间运算
SELECT CURRENT_DATE + INTERVAL ‘1 day’; — 明天
SELECT CURRENT_DATE – INTERVAL ‘1 week’; — 一周前
SELECT CURRENT_TIMESTAMP + INTERVAL ‘2 hours’; — 两小时后

— 日期时间截断
SELECT DATE_TRUNC(‘year’, CURRENT_DATE); — 截断到年
SELECT DATE_TRUNC(‘month’, CURRENT_DATE); — 截断到月
SELECT DATE_TRUNC(‘day’, CURRENT_TIMESTAMP); — 截断到日
SELECT DATE_TRUNC(‘hour’, CURRENT_TIMESTAMP); — 截断到小时

3.2.2 日期时间函数执行示例

— 日期时间函数执行示例

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;”

— 输出：
— current_date | current_time | current_timestamp
— —————+——————–+——————————————-
— 2026-04-07 | 10:45:30.123456 | 2026-04-07 10:45:30.123456+08
— (1 row)

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT EXTRACT(YEAR FROM CURRENT_DATE), EXTRACT(MONTH FROM CURRENT_DATE), EXTRACT(DAY FROM CURRENT_DATE);”

— 输出：
— date_part | date_part | date_part
— ———–+———–+———–
— 2026 | 4 | 7
— (1 row)

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT CURRENT_DATE + INTERVAL ‘1 day’ AS tomorrow, CURRENT_DATE – INTERVAL ‘1 week’ AS last_week;”

— 输出：
— tomorrow | last_week
— ————+————-
— 2026-04-08 | 2026-03-31
— (1 row)

3.3 日期时间格式化操作

3.3.1 日期时间格式化函数

— 日期时间格式化函数

— TO_CHAR函数：将日期时间转换为字符串
SELECT TO_CHAR(CURRENT_DATE, ‘YYYY-MM-DD’); — 2026-04-07
SELECT TO_CHAR(CURRENT_TIME, ‘HH24:MI:SS’); — 10:50:30
SELECT TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’); — 2026-04-07 10:50:30
SELECT TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY年MM月DD日 HH24时MI分SS秒’); — 2026年04月07日 10时50分30秒

— TO_DATE函数：将字符串转换为日期
SELECT TO_DATE(‘2026-04-07’, ‘YYYY-MM-DD’); — 2026-04-07
SELECT TO_DATE(‘2026年04月07日’, ‘YYYY年MM月DD日’); — 2026-04-07

— TO_TIMESTAMP函数：将字符串转换为时间戳
SELECT TO_TIMESTAMP(‘2026-04-07 10:50:30’, ‘YYYY-MM-DD HH24:MI:SS’); — 2026-04-07 10:50:30

3.3.2 日期时间格式化示例

— 日期时间格式化示例

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) AS formatted_time;”

— 输出：
— formatted_time
— ————————
— 2026-04-07 10:55:20
— (1 row)

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY年MM月DD日 HH24时MI分SS秒’) AS chinese_format;”

— 输出：
— chinese_format
— ——————————
— 2026年04月07日 10时55分20秒
— (1 row)

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT TO_DATE(‘2026-04-07’, ‘YYYY-MM-DD’) AS parsed_date;”

— 输出：
— parsed_date
— ————-
— 2026-04-07
— (1 row)

风哥提示：日期时间格式化是数据库操作中的常见需求，掌握TO_CHAR、TO_DATE和TO_TIMESTAMP函数的使用对于处理日期时间数据非常重要。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 日期时间类型使用案例

— 日期时间类型使用案例：订单管理系统

— 1. 创建订单表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_id VARCHAR(20) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
order_time TIME NOT NULL,
order_timestamp TIMESTAMP NOT NULL,
order_timestamptz TIMESTAMPTZ NOT NULL,
delivery_time TIMESTAMP,
order_status VARCHAR(20) DEFAULT ‘pending’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 2. 插入订单数据
INSERT INTO fgedu_orders (
order_id, customer_id, order_date, order_time,
order_timestamp, order_timestamptz, delivery_time
) VALUES
(‘ORD-20260407-001’, 101, ‘2026-04-07′, ’10:30:45’, ‘2026-04-07 10:30:45’, ‘2026-04-07 10:30:45+08’, ‘2026-04-07 14:30:00’),
(‘ORD-20260407-002’, 102, ‘2026-04-07′, ’11:15:30’, ‘2026-04-07 11:15:30’, ‘2026-04-07 11:15:30+08’, ‘2026-04-07 15:00:00’),
(‘ORD-20260407-003’, 103, ‘2026-04-07′, ’12:00:00’, ‘2026-04-07 12:00:00’, ‘2026-04-07 12:00:00+08’, ‘2026-04-07 16:00:00’);

— 3. 查询当天订单
SELECT * FROM fgedu_orders WHERE order_date = CURRENT_DATE;

— 4. 查询特定时间段订单
SELECT * FROM fgedu_orders WHERE order_timestamp BETWEEN ‘2026-04-07 10:00:00’ AND ‘2026-04-07 12:00:00’;

— 5. 更新订单状态和时间
UPDATE fgedu_orders SET order_status = ‘delivered’, delivery_time = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP WHERE order_id = ‘ORD-20260407-001’;

4.2 日期时间函数实战

— 日期时间函数实战：销售数据分析

— 1. 创建销售表
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price DECIMAL(10,2) NOT NULL,
sale_date DATE NOT NULL,
sale_time TIME NOT NULL,
sale_timestamp TIMESTAMP NOT NULL
);

— 2. 插入销售数据
INSERT INTO fgedu_sales (product_id, quantity, price, sale_date, sale_time, sale_timestamp) VALUES
(1, 2, 5999.99, ‘2026-04-01′, ’09:30:00’, ‘2026-04-01 09:30:00’),
(2, 1, 12999.99, ‘2026-04-01′, ’10:15:00’, ‘2026-04-01 10:15:00’),
(3, 3, 1999.99, ‘2026-04-02′, ’14:20:00’, ‘2026-04-02 14:20:00’),
(1, 1, 5999.99, ‘2026-04-03′, ’11:45:00’, ‘2026-04-03 11:45:00’),
(2, 2, 12999.99, ‘2026-04-04′, ’16:30:00’, ‘2026-04-04 16:30:00’),
(3, 5, 1999.99, ‘2026-04-05′, ’13:10:00’, ‘2026-04-05 13:10:00’),
(1, 3, 5999.99, ‘2026-04-06′, ’09:45:00’, ‘2026-04-06 09:45:00’),
(2, 1, 12999.99, ‘2026-04-07′, ’10:30:00’, ‘2026-04-07 10:30:00’);

— 3. 按天统计销售额
SELECT
sale_date,
SUM(quantity * price) AS daily_sales
FROM fgedu_sales
GROUP BY sale_date
ORDER BY sale_date;

— 4. 按周统计销售额
SELECT
DATE_TRUNC(‘week’, sale_date) AS week_start,
SUM(quantity * price) AS weekly_sales
FROM fgedu_sales
GROUP BY week_start
ORDER BY week_start;

— 5. 按月统计销售额
SELECT
DATE_TRUNC(‘month’, sale_date) AS month_start,
SUM(quantity * price) AS monthly_sales
FROM fgedu_sales
GROUP BY month_start
ORDER BY month_start;

— 6. 统计每天的销售数量
SELECT
sale_date,
COUNT(*) AS order_count,
SUM(quantity) AS total_quantity
FROM fgedu_sales
GROUP BY sale_date
ORDER BY sale_date;

4.3 日期时间索引优化案例

— 日期时间索引优化案例

— 1. 创建订单表（包含大量数据）
CREATE TABLE fgedu_large_orders (
id SERIAL PRIMARY KEY,
order_id VARCHAR(20) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
order_timestamp TIMESTAMP NOT NULL,
order_amount DECIMAL(10,2) NOT NULL
);

— 2. 插入测试数据（假设插入了100万条数据）
— 这里使用generate_series生成测试数据
INSERT INTO fgedu_large_orders (order_id, customer_id, order_date, order_timestamp, order_amount)
SELECT
‘ORD-‘ || TO_CHAR(d, ‘YYYYMMDD’) || ‘-‘ || LPAD(ROW_NUMBER() OVER (PARTITION BY d ORDER BY d)::text, 4, ‘0’),
(random() * 10000)::integer + 1,
d,
d + (random() * interval ’24 hours’),
(random() * 10000)::decimal(10,2)
FROM generate_series(‘2025-01-01’::date, ‘2026-12-31’::date, ‘1 day’) d,
generate_series(1, 1000) s;

— 3. 创建日期字段索引
CREATE INDEX idx_fgedu_large_orders_order_date ON fgedu_large_orders(order_date);
CREATE INDEX idx_fgedu_large_orders_order_timestamp ON fgedu_large_orders(order_timestamp);

— 4. 测试查询性能
— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_large_orders WHERE order_date = ‘2026-04-07’;”

— 输出：
— Bitmap Heap Scan on fgedu_large_orders (cost=4.32..201.59 rows=1000 width=44)
— Recheck Cond: (order_date = ‘2026-04-07’::date)
— -> Bitmap Index Scan on idx_fgedu_large_orders_order_date (cost=0.00..4.07 rows=1000 width=0)
— Index Cond: (order_date = ‘2026-04-07’::date)
— Execution Time: 0.523 ms

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_large_orders WHERE order_timestamp BETWEEN ‘2026-04-01’ AND ‘2026-04-30’;”

— 输出：
— Bitmap Heap Scan on fgedu_large_orders (cost=12.86..3034.00 rows=30000 width=44)
— Recheck Cond: (order_timestamp >= ‘2026-04-01 00:00:00’::timestamp without time zone AND order_timestamp <= '2026-04-30 00:00:00'::timestamp without time zone) -- -> Bitmap Index Scan on idx_fgedu_large_orders_order_timestamp (cost=0.00..5.36 rows=30000 width=0)
— Index Cond: (order_timestamp >= ‘2026-04-01 00:00:00’::timestamp without time zone AND order_timestamp <= '2026-04-30 00:00:00'::timestamp without time zone) -- Execution Time: 15.234 ms

Part05-风哥经验总结与分享

5.1 日期时间类型使用技巧

日期时间类型使用技巧：

类型选择：

根据业务需求选择合适的日期时间类型
需要时区支持时使用TIMESTAMPTZ
只需要日期或时间时使用DATE或TIME

精度设置：

根据业务需求设置合适的精度
一般业务场景使用毫秒精度即可
高精度场景使用微秒精度

时区处理：

统一使用UTC时间存储
应用层处理时区转换
避免在数据库中存储时区信息

索引优化：

为常用查询的日期时间字段创建索引
对于范围查询，使用B树索引
对于大量时间数据，使用分区表

5.2 日期时间常见问题解决

— 日期时间常见问题解决

— 1. 时区问题
— 问题：不同时区的时间显示不一致
— 解决：
— – 统一使用UTC时间存储
— – 设置数据库时区为UTC
— – 应用层处理时区转换

— 2. 日期时间格式问题
— 问题：插入日期时间数据时格式错误
— 解决：
— – 使用标准格式：YYYY-MM-DD HH24:MI:SS
— – 使用TO_DATE或TO_TIMESTAMP函数转换
— – 避免使用模糊的日期格式

— 3. 性能问题
— 问题：日期时间范围查询速度慢
— 解决：
— – 创建合适的索引
— – 使用DATE_TRUNC函数优化查询
— – 考虑使用分区表

— 4. 精度问题
— 问题：日期时间精度丢失
— 解决：
— – 明确指定精度，如TIMESTAMP(3)
— – 避免在应用层进行精度转换
— – 使用合适的日期时间函数

5.3 日期时间性能优化

— 日期时间性能优化

— 1. 索引优化
— – 为常用查询的日期时间字段创建索引
— – 使用部分索引减少索引大小
— – 对于频繁的范围查询，考虑使用BRIN索引

— 2. 查询优化
— – 使用DATE_TRUNC函数进行截断查询
— – 避免在WHERE子句中对日期时间字段使用函数
— – 使用索引覆盖查询减少IO

— 3. 存储优化
— – 选择合适的日期时间类型
— – 避免存储不必要的精度
— – 考虑使用整数类型存储时间戳（如UNIX时间戳）

— 4. 分区表优化
— – 对于大量时间数据，使用按时间分区
— – 分区键选择合适的时间粒度
— – 定期清理旧分区数据

风哥提示：日期时间类型是PostgreSQL中常用的数据类型，掌握它们的使用方法和优化技巧对于数据库性能和数据一致性非常重要。在生产环境中，应该根据业务需求选择合适的类型和精度，并进行合理的索引设计。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL优化 PostgreSQL性能 PostgreSQL管理数据库优化数据库管理