PostgreSQL日期时间

概述

PostgreSQL提供丰富的日期时间数据类型和函数，支持时区处理、时间间隔计算等高级功能。

日期时间数据类型

类型	存储大小	说明	范围
DATE	4字节	日期	公元前4713年至公元5874897年
TIME	8字节	时间（无时区）	00:00:00至24:00:00
TIMETZ	12字节	时间（带时区）	00:00:00+1559至24:00:00-1559
TIMESTAMP	8字节	日期时间（无时区）	公元前4713年至公元294276年
TIMESTAMPTZ	8字节	日期时间（带时区）	公元前4713年至公元294276年
INTERVAL	16字节	时间间隔	-178000000年至178000000年

获取当前日期时间

sql

-- 当前日期
SELECT CURRENT_DATE;              -- 2024-01-15
SELECT NOW()::DATE;               -- 2024-01-15

-- 当前时间
SELECT CURRENT_TIME;              -- 14:30:00.123456+08
SELECT LOCALTIME;                 -- 14:30:00.123456（无时区）

-- 当前时间戳
SELECT CURRENT_TIMESTAMP;         -- 2024-01-15 14:30:00.123456+08
SELECT NOW();                     -- 2024-01-15 14:30:00.123456+08
SELECT LOCALTIMESTAMP;            -- 2024-01-15 14:30:00.123456（无时区）

-- 事务开始时间
SELECT transaction_timestamp();
SELECT statement_timestamp();
SELECT clock_timestamp();         -- 实际当前时间（每次调用可能不同）

日期时间输入格式

sql

-- DATE格式
SELECT '2024-01-15'::DATE;
SELECT 'January 15, 2024'::DATE;
SELECT '01/15/2024'::DATE;
SELECT '20240115'::DATE;

-- TIME格式
SELECT '14:30:00'::TIME;
SELECT '2:30 PM'::TIME;
SELECT '143000'::TIME;

-- TIMESTAMP格式
SELECT '2024-01-15 14:30:00'::TIMESTAMP;
SELECT '2024-01-15T14:30:00'::TIMESTAMP;
SELECT 'January 15, 2024 2:30 PM'::TIMESTAMP;

-- 带时区
SELECT '2024-01-15 14:30:00+08'::TIMESTAMPTZ;
SELECT '2024-01-15 14:30:00 Asia/Shanghai'::TIMESTAMPTZ;

EXTRACT函数

提取日期时间的各个部分：

sql

SELECT EXTRACT(YEAR FROM TIMESTAMP '2024-01-15 14:30:00');    -- 2024
SELECT EXTRACT(MONTH FROM TIMESTAMP '2024-01-15 14:30:00');   -- 1
SELECT EXTRACT(DAY FROM TIMESTAMP '2024-01-15 14:30:00');     -- 15
SELECT EXTRACT(HOUR FROM TIMESTAMP '2024-01-15 14:30:00');    -- 14
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2024-01-15 14:30:00');  -- 30
SELECT EXTRACT(SECOND FROM TIMESTAMP '2024-01-15 14:30:00');  -- 0

-- 其他提取选项
SELECT EXTRACT(DOW FROM DATE '2024-01-15');      -- 星期几（0=周日）
SELECT EXTRACT(DOY FROM DATE '2024-01-15');      -- 一年中的第几天
SELECT EXTRACT(WEEK FROM DATE '2024-01-15');     -- 第几周
SELECT EXTRACT(QUARTER FROM DATE '2024-01-15');  -- 第几季度
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-01-15 14:30:00');  -- Unix时间戳

DATE_PART函数

与EXTRACT类似：

sql

SELECT DATE_PART('year', TIMESTAMP '2024-01-15 14:30:00');   -- 2024
SELECT DATE_PART('month', TIMESTAMP '2024-01-15 14:30:00');  -- 1
SELECT DATE_PART('day', TIMESTAMP '2024-01-15 14:30:00');    -- 15

日期时间运算

使用INTERVAL

sql

-- 加减天数
SELECT DATE '2024-01-15' + INTERVAL '7 days';       -- 2024-01-22
SELECT DATE '2024-01-15' - INTERVAL '7 days';       -- 2024-01-08

-- 加减月份
SELECT DATE '2024-01-15' + INTERVAL '1 month';      -- 2024-02-15
SELECT DATE '2024-01-15' + INTERVAL '3 months';     -- 2024-04-15

-- 加减年份
SELECT DATE '2024-01-15' + INTERVAL '1 year';       -- 2025-01-15

-- 加减时间
SELECT TIMESTAMP '2024-01-15 14:30:00' + INTERVAL '2 hours';    -- 16:30:00
SELECT TIMESTAMP '2024-01-15 14:30:00' + INTERVAL '30 minutes'; -- 15:00:00

-- 复合间隔
SELECT DATE '2024-01-15' + INTERVAL '1 year 2 months 3 days';

日期差值

sql

-- 两个日期的差值（天数）
SELECT DATE '2024-01-20' - DATE '2024-01-15';  -- 5

-- 两个时间戳的差值（INTERVAL）
SELECT TIMESTAMP '2024-01-20 10:00' - TIMESTAMP '2024-01-15 08:00';
-- 5 days 02:00:00

-- 计算年龄
SELECT AGE(TIMESTAMP '2024-01-15', TIMESTAMP '2000-06-20');
-- 23 years 6 mons 25 days

日期时间格式化

TO_CHAR函数

sql

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');           -- 2024-01-15
SELECT TO_CHAR(NOW(), 'YYYY年MM月DD日');        -- 2024年01月15日
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- 2024-01-15 14:30:00
SELECT TO_CHAR(NOW(), 'Day, Month DD, YYYY');   -- Monday, January 15, 2024
SELECT TO_CHAR(NOW(), 'HH:MI:SS AM');           -- 02:30:00 PM

常用格式化模式

模式	说明	示例
YYYY	4位年份	2024
MM	月份（01-12）	01
DD	日（01-31）	15
HH24	24小时制	14
HH12/HH	12小时制	02
MI	分钟	30
SS	秒	00
AM/PM	上午/下午	PM
Day	星期全称	Monday
Mon	月份缩写	Jan

字符串转日期

sql

SELECT TO_DATE('15-01-2024', 'DD-MM-YYYY');
SELECT TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

日期截断

sql

-- 截断到日
SELECT DATE_TRUNC('day', TIMESTAMP '2024-01-15 14:30:45');
-- 2024-01-15 00:00:00

-- 截断到月
SELECT DATE_TRUNC('month', TIMESTAMP '2024-01-15 14:30:45');
-- 2024-01-01 00:00:00

-- 截断到年
SELECT DATE_TRUNC('year', TIMESTAMP '2024-01-15 14:30:45');
-- 2024-01-01 00:00:00

-- 截断到小时
SELECT DATE_TRUNC('hour', TIMESTAMP '2024-01-15 14:30:45');
-- 2024-01-15 14:00:00

时区处理

sql

-- 设置时区
SET TIME ZONE 'Asia/Shanghai';
SET TIME ZONE 'UTC';

-- 查看当前时区
SHOW TIME ZONE;

-- 时区转换
SELECT TIMESTAMP '2024-01-15 14:30:00' AT TIME ZONE 'Asia/Shanghai';
SELECT TIMESTAMP WITH TIME ZONE '2024-01-15 14:30:00+08'
    AT TIME ZONE 'UTC';

-- 创建带时区的时间戳
SELECT '2024-01-15 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai';

实际应用示例

查询特定日期范围

sql

-- 今天的订单
SELECT * FROM orders
WHERE created_at::DATE = CURRENT_DATE;

-- 本月的订单
SELECT * FROM orders
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND created_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

-- 最近7天
SELECT * FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

按时间分组统计

sql

-- 按月统计
SELECT
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- 按小时统计
SELECT
    EXTRACT(HOUR FROM created_at) as hour,
    COUNT(*) as order_count
FROM orders
GROUP BY EXTRACT(HOUR FROM created_at)
ORDER BY hour;

计算工作日

sql

-- 排除周末的日期计算
SELECT COUNT(*)
FROM generate_series(
    DATE '2024-01-01',
    DATE '2024-01-31',
    INTERVAL '1 day'
) AS day
WHERE EXTRACT(DOW FROM day) NOT IN (0, 6);  -- 排除周日(0)和周六(6)

小结

日期时间类型的主要功能包括：

DATE、TIME、TIMESTAMP、TIMESTAMPTZ、INTERVAL
使用 EXTRACT 提取部分
算术运算
使用 TO_CHAR 格式化
时区支持

PostgreSQL日期时间 ​

概述 ​

日期时间数据类型 ​

获取当前日期时间 ​

日期时间输入格式 ​

EXTRACT函数 ​

DATE_PART函数 ​

日期时间运算 ​

使用INTERVAL ​

日期差值 ​

日期时间格式化 ​

TO_CHAR函数 ​

常用格式化模式 ​

字符串转日期 ​

日期截断 ​

时区处理 ​

实际应用示例 ​

查询特定日期范围 ​

按时间分组统计 ​

计算工作日 ​

小结 ​

PostgreSQL日期时间

概述

日期时间数据类型

获取当前日期时间

日期时间输入格式

EXTRACT函数

DATE_PART函数

日期时间运算

使用INTERVAL

日期差值

日期时间格式化

TO_CHAR函数

常用格式化模式

字符串转日期

日期截断

时区处理

实际应用示例

查询特定日期范围

按时间分组统计

计算工作日

小结