PHP小码农

Appearance

DATE / TIME / TIMESTAMP ​

本节讲解 PostgreSQL 的日期与时间基础类型 DATE、TIME、TIMESTAMP。你将掌握它们的取值范围、精度、输入输出格式与常用运算，为后续理解 TIMESTAMPTZ 与时区处理打下基础。

前置知识

阅读本节前，建议先了解：字符集与编码

基础概念 ​

PostgreSQL 的日期时间类型可分为"带时区"与"不带时区"两大族：

类型名别名存储空间取值范围精度说明
DATE—4 字节4713 BC ~ 5874897 AD1 天只有日期
TIMETIME WITHOUT TIME ZONE8 字节00:00:00 ~ 24:00:001 微秒只有时间，不含时区
TIME WITH TIME ZONETIMETZ12 字节00:00:00+15:59 ~ 24:00:00-15:591 微秒时间 + 时区偏移（不推荐）
TIMESTAMPTIMESTAMP WITHOUT TIME ZONE8 字节4713 BC ~ 294276 AD1 微秒日期 + 时间，不含时区
TIMESTAMP WITH TIME ZONETIMESTAMPTZ8 字节4713 BC ~ 294276 AD1 微秒日期 + 时间 + 时区语义
INTERVAL—16 字节±178000000 年1 微秒时间间隔

关键术语：

  • 时间戳（Timestamp）：一个具体的日期时间点。
  • 微秒（Microsecond）：PostgreSQL 的默认精度上限，即小数点后 6 位。
  • 不带时区（Without Time Zone）：字面量含义，不携带任何时区信息。
  • 带时区（With Time Zone）：内部统一存 UTC，显示时按会话时区换算。
  • 儒略日（Julian Day）：PostgreSQL 内部用整数表示日期，DATE '2000-01-01' 对应儒略日 2451545。

两个 TIMESTAMP 类型的名字容易误解：

sql
SELECT
  pg_typeof('2026-09-22 10:00:00'::timestamp)      AS ts_type,
  pg_typeof('2026-09-22 10:00:00+08'::timestamptz) AS tstz_type;
text
 ts_type   | tstz_type
-----------+------------------------
 timestamp | timestamp with time zone

TIMESTAMP 的完整名字是 TIMESTAMP WITHOUT TIME ZONE，TIMESTAMPTZ 是 TIMESTAMP WITH TIME ZONE 的别名。类型名中的 WITH/WITHOUT TIME ZONE 描述的是"是否携带时区信息"，而不是"存不存时区"——TIMESTAMPTZ 内部也不存时区，只存 UTC 瞬间值，显示时按会话时区换算。

语法与用法 ​

text
-- 类型声明
列名 DATE | TIME [(p)] | TIMESTAMP [(p)] | TIMESTAMPTZ [(p)] | INTERVAL

-- p 表示小数秒精度，0~6，默认 6

-- 字面量
DATE '2026-09-22'
TIME '10:30:00'
TIMESTAMP '2026-09-22 10:30:00'
TIMESTAMPTZ '2026-09-22 10:30:00+08:00'

-- 简写：字符串会按上下文自动转换
'2026-09-22'::date
'10:30:00'::time
'2026-09-22 10:30:00'::timestamp

-- 当前时刻
CURRENT_DATE          -- 会话时区下的今天（DATE）
CURRENT_TIME          -- 会话时区下的当前时间（TIMETZ）
CURRENT_TIMESTAMP     -- 事务开始时刻（TIMESTAMPTZ）
LOCALTIME             -- 会话时区下的当前时间（TIME）
LOCALTIMESTAMP        -- 会话时区下的当前时间（TIMESTAMP）
now()                 -- 等价于 CURRENT_TIMESTAMP
clock_timestamp()     -- 实际当前时刻（同一语句内可变）
statement_timestamp() -- 语句开始时刻
transaction_timestamp() -- 事务开始时刻（同 now()）

最简可运行示例：

sql
SELECT
  DATE '2026-09-22'                     AS a_date,
  TIME '10:30:00'                       AS a_time,
  TIMESTAMP '2026-09-22 10:30:00'       AS a_ts,
  TIMESTAMPTZ '2026-09-22 10:30:00+08'  AS a_tstz;
text
   a_date   | a_time   |        a_ts         |        a_tstz
------------+----------+---------------------+------------------------
 2026-09-22 | 10:30:00 | 2026-09-22 10:30:00 | 2026-09-22 10:30:00+08

详细说明 ​

精度指定：小数秒 ​

TIME、TIMESTAMP 可以指定小数秒精度 p（0~6）。声明精度会导致存储时四舍五入，但不改变存储大小（仍是 8 字节）：

sql
SELECT
  TIMESTAMP(0) '2026-09-22 10:30:15.678901' AS p0,
  TIMESTAMP(3) '2026-09-22 10:30:15.678901' AS p3,
  TIMESTAMP(6) '2026-09-22 10:30:15.678901' AS p6;
text
         p0          |         p3          |           p6
---------------------+---------------------+-------------------------
 2026-09-22 10:30:16 | 2026-09-22 10:30:15.679 | 2026-09-22 10:30:15.678901

p=0 时 .678901 被四舍五入到下一秒（15 → 16）。

存储空间不受精度影响：

sql
SELECT
  pg_column_size(TIMESTAMP(0) '2026-09-22 10:30:15') AS size_p0,
  pg_column_size(TIMESTAMP(6) '2026-09-22 10:30:15.678901') AS size_p6;
text
 size_p0 | size_p6
---------+---------
       8 |       8

输入格式：几乎任意顺序 ​

PostgreSQL 的日期时间输入解析器极其宽松，支持多种格式：

sql
SELECT
  '2026-09-22'::date                  AS iso,
  '2026/09/22'::date                  AS slash,
  '09-22-2026'::date                  AS us_style,        -- 依赖 DateStyle
  'Sep 22, 2026'::date                AS month_name,
  '22 Sep 2026'::date                 AS day_first,
  '20260922'::date                    AS compact;
text
    iso     |   slash    |  us_style  | month_name | day_first  |  compact
------------+------------+------------+------------+------------+------------
 2026-09-22 | 2026-09-22 | 2026-09-22 | 2026-09-22 | 2026-09-22 | 2026-09-22

上面的 '09-22-2026' 之所以正确解析，是因为 DateStyle 默认是 ISO, MDY。这非常危险——同一条 SQL 在 DMY 环境下会解析失败或得到不同日期：

sql
SHOW DateStyle;
SET DateStyle TO 'ISO, DMY';
SELECT '09-22-2026'::date AS with_dmy;
text
 DateStyle
-----------
 ISO, MDY

ERROR:  date/time field value out of range: "09-22-2026"
sql
RESET DateStyle;

实践结论：永远使用 YYYY-MM-DD 格式，它不受 DateStyle 影响。

DateStyle 与 IntervalStyle 会话参数 ​

sql
SHOW DateStyle;         -- 默认 ISO, MDY
SHOW IntervalStyle;     -- 默认 postgres
SHOW TimeZone;          -- 默认由 initdb 的 locale 决定
DateStyle 取值输出示例
ISO, MDY2026-09-22 10:30:00
SQL, MDY09/22/2026 10:30:00
Postgres, MDYTue Sep 22 10:30:00 2026
German, DMY22.09.2026 10:30:00
sql
SET DateStyle TO 'Postgres, MDY';
SELECT TIMESTAMP '2026-09-22 10:30:00' AS formatted;
text
          formatted
------------------------------
 Tue Sep 22 10:30:00 2026 CST
sql
RESET DateStyle;

应用连接的 client_encoding 与 DateStyle 都应在连接串中显式固定：

text
# JDBC
jdbc:postgresql://host/demo?DateStyle=ISO&TimeZone=Asia/Shanghai

日期时间运算 ​

日期时间类型支持与 INTERVAL 的加减、类型间的相减：

运算结果类型示例
DATE + INTEGERDATEDATE '2026-09-22' + 7 → 2026-09-29
DATE - INTEGERDATEDATE '2026-09-22' - 7 → 2026-09-15
DATE - DATEINTEGER相差天数
TIMESTAMP ± INTERVALTIMESTAMPTIMESTAMP '...' + INTERVAL '1 day'
TIMESTAMP - TIMESTAMPINTERVAL相差间隔
DATE + TIMETIMESTAMP组合成时间戳
INTERVAL * NUMBERINTERVAL间隔缩放
DATE ± TIMETIMESTAMP—
sql
SELECT
  DATE '2026-09-22' + 7                 AS plus_days,
  DATE '2026-09-22' - 7                 AS minus_days,
  DATE '2026-09-22' - DATE '2026-09-01' AS diff_days,
  TIMESTAMP '2026-09-22 10:30:00' + INTERVAL '90 minutes' AS plus_interval,
  TIMESTAMP '2026-09-22 12:00:00' - TIMESTAMP '2026-09-22 10:30:00' AS diff_interval,
  DATE '2026-09-22' + TIME '10:30:00'   AS date_plus_time;
text
 plus_days  | minus_days | diff_days |   plus_interval     | diff_interval |   date_plus_time
------------+------------+-----------+---------------------+---------------+---------------------
 2026-09-29 | 2026-09-15 |        21 | 2026-09-22 12:00:00 | 01:30:00      | 2026-09-22 10:30:00

减去两个 DATE 得到整数天数，而减去两个 TIMESTAMP 得到 INTERVAL。这个差异会影响聚合写法：

sql
-- 求平均间隔天数：先破坏为 INTERVAL 再取天数
SELECT
  avg(DATE '2026-09-22' - DATE '2026-09-01')  AS avg_days_int,
  avg(TIMESTAMP '2026-09-22 12:00:00' - TIMESTAMP '2026-09-22 10:30:00') AS avg_interval;
text
 avg_days_int | avg_interval
--------------+--------------
           21 | 01:30:00

CURRENT_TIMESTAMP 系列：事务时刻 vs 实际时刻 ​

这是生产环境最常见的语义陷阱之一。PostgreSQL 提供三类"当前时刻"函数：

函数取值时刻同一事务内同一语句内
now() / CURRENT_TIMESTAMP / transaction_timestamp()事务开始恒定不变恒定不变
statement_timestamp()语句开始可变恒定不变
clock_timestamp()函数调用瞬间可变可变
timeofday()函数调用瞬间可变可变（返回 TEXT）
sql
BEGIN;
SELECT pg_sleep(0.1);
SELECT
  now()                  AS txn_time,
  statement_timestamp()  AS stmt_time,
  clock_timestamp()      AS clock_time;
SELECT pg_sleep(0.1);
SELECT
  now()                  AS txn_time_2,
  clock_timestamp()      AS clock_time_2;
COMMIT;
text
          txn_time          |         stmt_time         |        clock_time
----------------------------+---------------------------+---------------------------
 2026-09-22 10:30:00.123456 | 2026-09-22 10:30:00.223456 | 2026-09-22 10:30:00.223789

         txn_time_2         |       clock_time_2
----------------------------+---------------------------
 2026-09-22 10:30:00.123456 | 2026-09-22 10:30:00.324567

同一事务内 now() 完全不变，这是 PostgreSQL 的有意设计：保证一个事务内看到一致的"事务时间"，从而让基于 now() 的逻辑可重现。

含义对比：

sql
SELECT
  now()                     AS a,
  now()                     AS b,          -- a 与 b 必然相等
  clock_timestamp()         AS c,
  clock_timestamp()         AS d;          -- c 与 d 可能不等
text
             a              |             b              |              c              |              d
----------------------------+----------------------------+-----------------------------+-----------------------------
 2026-09-22 10:30:00.123456 | 2026-09-22 10:30:00.123456 | 2026-09-22 10:30:00.123789  | 2026-09-22 10:30:00.123801

建议：默认列默认值用 now()；只有性能基准测试、审计耗时统计才用 clock_timestamp()。

CURRENT_DATE / LOCALTIME / LOCALTIMESTAMP 都是语句级的（等价于 statement_timestamp 转换而来）：

sql
BEGIN;
SELECT now()::date AS d1, CURRENT_DATE AS d2;
SELECT pg_sleep(0.1);
SELECT now()::date AS d3, CURRENT_DATE AS d4;
COMMIT;
text
 d1     | d2     | d3     | d4
--------+--------+--------+--------
 2026-09-22 | 2026-09-22 | 2026-09-22 | 2026-09-22

特殊值与无穷 ​

PostgreSQL 支持"无穷"日期时间，用于表示"永远有效"或"未知"：

sql
SELECT
  'infinity'::timestamp       AS ts_infinity,
  '-infinity'::timestamp      AS ts_neg_infinity,
  'infinity'::date            AS d_infinity,
  'infinity'::timestamp - now() AS huge_interval;
text
  ts_infinity | ts_neg_infinity | d_infinity | huge_interval
--------------+-----------------+------------+----------------
 infinity     | -infinity       | infinity   | infinity

infinity 与 -infinity 参与比较时有明确顺序：

sql
SELECT
  '-infinity'::timestamp < now()      AS neg_is_min,
  'infinity'::timestamp > now()       AS inf_is_max,
  'infinity'::timestamp = 'infinity'::timestamp AS inf_eq;
text
 neg_is_min | inf_is_max | inf_eq
------------+------------+--------
 t          | t          | t

适用场景：有效期区间的开闭（valid_to = 'infinity' 表示仍有效），比用 NULL 更易写 BETWEEN：

sql
SELECT * FROM price_history
WHERE now() BETWEEN valid_from AND valid_to;

注意：infinity 参与 to_char 会输出 infinity 文本；转 INTEGER 会报错：

sql
SELECT 'infinity'::date::integer;
text
ERROR:  cannot convert infinity to integer

从字符串解析时的严格与宽松 ​

PostgreSQL 输入解析非常宽松，会接受一些"看起来不对"的输入：

sql
SELECT
  '2026-09-31'::date,          -- 9 月只有 30 天
  '2026-02-30'::date,          -- 2 月只有 28 天
  '25:00:00'::time;            -- 时间超过 24 小时
text
ERROR:  date/time field value out of range: "2026-09-31"

但时间部分允许 24:00:00，也表示次日的 00:00：

sql
SELECT
  '24:00:00'::time AS end_of_day,
  '24:00:00'::time = '00:00:00'::time AS is_midnight;
text
 end_of_day | is_midnight
------------+-------------
 24:00:00   | t

'24:00:00'::time 与 '00:00:00'::time 比较相等，但显示不同。这在存储营业时间（09:00~24:00）时会带来微妙问题：

sql
SELECT
  TIME '24:00:00' AS store_close,
  TIME '09:00:00' < TIME '24:00:00' AS before_close,
  TIME '23:59:59' < TIME '24:00:00' AS ok;
text
 store_close | before_close | ok
-------------+--------------+----
 24:00:00    | t            | t

常用提取函数 ​

extract 与 date_part 从日期时间中提取字段：

sql
SELECT
  extract(year   FROM TIMESTAMP '2026-09-22 10:30:45.123456') AS y,
  extract(month  FROM TIMESTAMP '2026-09-22 10:30:45.123456') AS mo,
  extract(day    FROM TIMESTAMP '2026-09-22 10:30:45.123456') AS d,
  extract(hour   FROM TIMESTAMP '2026-09-22 10:30:45.123456') AS h,
  extract(minute FROM TIMESTAMP '2026-09-22 10:30:45.123456') AS mi,
  extract(second FROM TIMESTAMP '2026-09-22 10:30:45.123456') AS s,
  extract(milliseconds FROM TIMESTAMP '2026-09-22 10:30:45.123456') AS ms,
  extract(microseconds FROM TIMESTAMP '2026-09-22 10:30:45.123456') AS us;
text
 y    | mo | d  | h  | mi |       s        |    ms    |      us
------+----+----+----+----+----------------+----------+---------------
 2026 |  9 | 22 | 10 | 30 | 45.123456      | 45123.456 | 45123456.000000

extract 返回 NUMERIC（PostgreSQL 14+），而 date_part 返回 DOUBLE PRECISION。这是重要差异：

sql
SELECT
  pg_typeof(extract(second FROM now()))  AS extract_type,
  pg_typeof(date_part('second', now()))  AS date_part_type;
text
 extract_type | date_part_type
--------------+----------------
 numeric      | double precision

PostgreSQL 14 之前 extract 也返回 double precision，改类型是为了让 extract(epoch FROM ...) 等场景避免浮点误差。

其他常用提取方式：

sql
SELECT
  extract(dow   FROM DATE '2026-09-22') AS day_of_week,   -- 0=Sunday
  extract(isodow FROM DATE '2026-09-22') AS iso_dow,      -- 1=Monday
  extract(doy   FROM DATE '2026-09-22') AS day_of_year,
  extract(week  FROM DATE '2026-09-22') AS iso_week,
  extract(quarter FROM DATE '2026-09-22') AS quarter,
  extract(epoch FROM TIMESTAMP '2026-09-22 10:30:00') AS epoch;
text
 day_of_week | iso_dow | day_of_year | iso_week | quarter |  epoch
-------------+---------+-------------+----------+---------+------------
           2 |       2 |         265 |       39 |       3 | 1789967400

epoch 是从 1970-01-01 00:00:00 UTC 起的秒数，是跨系统交换时间的通用方案。

类型截断：date_trunc ​

date_trunc 把时间戳截断到指定精度：

sql
SELECT
  date_trunc('year',    TIMESTAMP '2026-09-22 10:30:45.123456') AS y,
  date_trunc('quarter', TIMESTAMP '2026-09-22 10:30:45.123456') AS q,
  date_trunc('month',   TIMESTAMP '2026-09-22 10:30:45.123456') AS mo,
  date_trunc('week',    TIMESTAMP '2026-09-22 10:30:45.123456') AS w,
  date_trunc('day',     TIMESTAMP '2026-09-22 10:30:45.123456') AS d,
  date_trunc('hour',    TIMESTAMP '2026-09-22 10:30:45.123456') AS h;
text
          y          |          q          |         mo          |          w          |         d           |          h
---------------------+---------------------+---------------------+---------------------+---------------------+---------------------
 2026-01-01 00:00:00 | 2026-07-01 00:00:00 | 2026-09-01 00:00:00 | 2026-09-21 00:00:00 | 2026-09-22 00:00:00 | 2026-09-22 10:00:00

date_trunc('week', ...) 按 ISO 周起算，即周一为一周开始。2026-09-22 是周二，所以截断到 2026-09-21（周一）。

组合 date_trunc 是时间序列聚合的标准写法：

sql
SELECT
  date_trunc('day', created_at) AS day,
  count(*)                       AS order_count
FROM orders
GROUP BY date_trunc('day', created_at)
ORDER BY day;

实战示例 ​

以下示例在 demo 库中按顺序执行。

第 1 步：建订单表

sql
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no      VARCHAR(32) NOT NULL UNIQUE,
  amount        NUMERIC(12,2) NOT NULL,
  order_date    DATE        NOT NULL,
  created_at    TIMESTAMP   NOT NULL DEFAULT now(),
  delivery_time TIME,
  remark        TEXT
);

INSERT INTO orders (order_no, amount, order_date, created_at, delivery_time) VALUES
  ('SO20260922001', 1999.00, '2026-09-22', '2026-09-22 09:15:30', '14:00:00'),
  ('SO20260922002',  299.50, '2026-09-22', '2026-09-22 10:40:12', '16:30:00'),
  ('SO20260921001',  880.00, '2026-09-21', '2026-09-21 20:05:00', '09:00:00'),
  ('SO20260920001',  120.00, '2026-09-20', '2026-09-20 08:00:00', NULL);

第 2 步：基础字段提取

sql
SELECT
  order_no,
  order_date,
  created_at,
  extract(year  FROM created_at) AS y,
  extract(month FROM created_at) AS mo,
  extract(day   FROM created_at) AS d,
  extract(hour  FROM created_at) AS h,
  extract(isodow FROM created_at) AS iso_dow
FROM orders
ORDER BY created_at;
text
    order_no    | order_date |     created_at      |  y   | mo | d  | h  | iso_dow
----------------+------------+---------------------+------+----+----+----+---------
 SO20260920001  | 2026-09-20 | 2026-09-20 08:00:00 | 2026 |  9 | 20 |  8 |       7
 SO20260921001  | 2026-09-21 | 2026-09-21 20:05:00 | 2026 |  9 | 21 | 20 |       1
 SO20260922001  | 2026-09-22 | 2026-09-22 09:15:30 | 2026 |  9 | 22 |  9 |       2
 SO20260922002  | 2026-09-22 | 2026-09-22 10:40:12 | 2026 |  9 | 22 | 10 |       2

第 3 步：按天聚合订单

sql
SELECT
  order_date,
  count(*)          AS cnt,
  sum(amount)       AS total
FROM orders
GROUP BY order_date
ORDER BY order_date;
text
 order_date | cnt |  total
------------+-----+---------
 2026-09-20 |   1 |  120.00
 2026-09-21 |   1 |  880.00
 2026-09-22 |   2 | 2298.50

第 4 步：计算订单距今时长

sql
SELECT
  order_no,
  order_date,
  TIMESTAMP '2026-09-22 12:00:00' - created_at AS age,
  extract(epoch FROM TIMESTAMP '2026-09-22 12:00:00' - created_at) AS age_seconds,
  extract(day FROM TIMESTAMP '2026-09-22 12:00:00' - created_at) AS age_days
FROM orders
ORDER BY created_at;
text
    order_no    | order_date |   age    | age_seconds | age_days
----------------+------------+----------+-------------+----------
 SO20260920001  | 2026-09-20 | 2 days 04:00:00 |      187200 |        2
 SO20260921001  | 2026-09-21 | 15:55:00 |       57300 |        0
 SO20260922001  | 2026-09-22 | 02:44:30 |        9870 |        0
 SO20260922002  | 2026-09-22 | 01:19:48 |        4788 |        0

extract(day FROM interval) 只返回"天数部分"，不是"总天数"。上面 15:55:00 的天数部分是 0，这就是 age_days 全为 0 的原因。要取总天数需用 epoch / 86400。

第 5 步：按小时统计下单分布

sql
SELECT
  extract(hour FROM created_at)::integer AS hour_of_day,
  count(*)                                AS cnt
FROM orders
GROUP BY extract(hour FROM created_at)
ORDER BY hour_of_day;
text
 hour_of_day | cnt
-------------+-----
           8 |   1
           9 |   1
          10 |   1
          20 |   1

第 6 步：date_trunc 按周聚合

sql
SELECT
  date_trunc('week', order_date)::date AS week_start,
  count(*)                             AS cnt,
  sum(amount)                          AS total
FROM orders
GROUP BY date_trunc('week', order_date)
ORDER BY week_start;
text
 week_start | cnt |  total
------------+-----+---------
 2026-09-14 |   1 |  120.00
 2026-09-21 |   3 | 3178.50

2026-09-20 是周日，属于 2026-09-14（周一）开始的 ISO 周。

第 7 步：日期加减与工作日计算

sql
SELECT
  order_no,
  order_date,
  order_date + 7                        AS plus_week,
  order_date + INTERVAL '1 month'       AS plus_month,
  (order_date + INTERVAL '1 month')::date AS plus_month_date
FROM orders
ORDER BY order_date
LIMIT 2;
text
    order_no    | order_date | plus_week  |     plus_month      | plus_month_date
----------------+------------+------------+---------------------+-----------------
 SO20260920001  | 2026-09-20 | 2026-09-27 | 2026-10-20 00:00:00 | 2026-10-20
 SO20260921001  | 2026-09-21 | 2026-09-28 | 2026-10-21 00:00:00 | 2026-10-21

第 8 步：月末与月初边界检查（陷阱演示）

sql
SELECT
  DATE '2026-01-31' + INTERVAL '1 month' AS jan31_plus_1m,
  DATE '2026-03-31' + INTERVAL '1 month' AS mar31_plus_1m,
  DATE '2026-02-28' + INTERVAL '1 month' AS feb28_plus_1m;
text
 jan31_plus_1m       | mar31_plus_1m       | feb28_plus_1m
---------------------+---------------------+---------------------
 2026-02-28 00:00:00 | 2026-04-30 00:00:00 | 2026-03-28 00:00:00

DATE + INTERVAL '1 month' 会把日期截断到目标月的有效天数（1 月 31 日加 1 月得 2 月 28 日），这个行为符合直觉，但需要留意 2026-02-28 + 1 month 得到 2026-03-28 而非月末。

注意事项 ​

1. TIMESTAMP 不含时区，跨时区系统会出错

sql
-- 中国用户在东八区存入 10:30
INSERT INTO events (occurred_at) VALUES (TIMESTAMP '2026-09-22 10:30:00');

-- 美国用户查询看到 10:30，但实际相差 12 小时
SELECT occurred_at FROM events;

语义上 TIMESTAMP 表示"墙上时钟时间"，不承载绝对时间点。跨时区业务必须用 TIMESTAMPTZ。

2. CURRENT_TIMESTAMP 在同一事务内不变

若在长事务中需要真实当前时间（如性能日志），必须用 clock_timestamp()：

sql
BEGIN;
SELECT now() AS txn_start;
SELECT pg_sleep(5);
SELECT now() AS still_same, clock_timestamp() AS real_now;
COMMIT;
text
 txn_start                  | still_same                 | real_now
----------------------------+----------------------------+---------------------------
 2026-09-22 10:30:00.123456 | 2026-09-22 10:30:00.123456 | 2026-09-22 10:30:05.234567

3. DateStyle 影响解析与显示

sql
SET DateStyle TO 'SQL, DMY';
SELECT TIMESTAMP '22/09/2026 10:30:00';
RESET DateStyle;

输入输出都会变。统一使用 ISO 格式是最安全的做法。

4. '24:00:00'::time 等于 '00:00:00'::time

sql
SELECT
  TIME '24:00:00' = TIME '00:00:00'  AS equal,
  TIME '24:00:00'::text              AS shown;
text
 equal |  shown
-------+----------
 t     | 24:00:00

相等但文本不同，用 DISTINCT 或 GROUP BY 时会合并，用字符串比较会不同。

5. extract 与 date_part 返回类型不同

sql
SELECT
  pg_typeof(extract(month FROM now()))              AS extract_t,
  pg_typeof(date_part('month', now()))              AS date_part_t;
text
 extract_t |    date_part_t
-----------+--------------------
 numeric   | double precision

PostgreSQL 14 起 extract 返回 numeric。混用两者可能带来类型不匹配的报错，建议全项目统一用一种。

6. date_trunc('week', ...) 是周一起算

sql
SELECT
  extract(isodow FROM DATE '2026-09-20') AS sunday_dow,
  date_trunc('week', DATE '2026-09-20')  AS week_start;
text
 sunday_dow |      week_start
------------+---------------------
          7 | 2026-09-14 00:00:00

若业务周从周日开始，需手动调整：

sql
SELECT date_trunc('week', DATE '2026-09-20' + 1) - 1 AS sunday_starting_week;

7. 时间戳字段建索引时注意函数包裹

sql
-- 无法使用 created_at 上的普通索引
SELECT * FROM orders WHERE created_at::date = '2026-09-22';

-- 可以走索引
SELECT * FROM orders
WHERE created_at >= '2026-09-22 00:00:00' AND created_at < '2026-09-23 00:00:00';

第二种写法（半开区间）是标准做法，也避免了 BETWEEN 在含小数秒时的边界问题。

8. infinity 参与运算需谨慎

sql
SELECT 'infinity'::timestamp - 'infinity'::timestamp;
text
ERROR:  interval out of range

infinity - infinity 报错而不是返回 0 或 NaN。

9. 时区未设时的 CURRENT_DATE 依赖服务器时区

sql
SHOW TimeZone;
SELECT CURRENT_DATE;

服务器时区可能是 UTC，而业务在中国，跨零点时会得到"昨天"的日期：

sql
SET TimeZone TO 'UTC';
SELECT now()::date AS utc_date, (now() AT TIME ZONE 'Asia/Shanghai')::date AS cn_date;
text
 utc_date   |  cn_date
------------+------------
 2026-09-22 | 2026-09-22

10. TIMESTAMP 精度声明不影响存储大小，但影响 pg_column_size 语义

列声明为 TIMESTAMP(0) 时插入值会被四舍五入，这在"分钟级精度足够"的业务中反而有利——减少无意义的微秒差异导致的分组碎片。

最佳实践 ​

1. 需要绝对时间点用 TIMESTAMPTZ，只表示民用日历用 TIMESTAMP

场景推荐类型
订单创建时间、日志时间、审计时间TIMESTAMPTZ
生日、节假日、营业日历DATE
营业时间（每天 9:00~18:00）TIME
某人的日程"9 点开会"（跨时区需另存时区）TIMESTAMP + 时区列
有效期区间TIMESTAMPTZ + infinity

2. 列默认值用 now()，不要用 CURRENT_TIMESTAMP 以外的表达式

sql
CREATE TABLE events (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload    JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

3. 日期范围查询统一用半开区间

sql
-- 推荐
SELECT * FROM orders
WHERE created_at >= DATE '2026-09-01'
  AND created_at <  DATE '2026-10-01';

-- 不推荐：边界含微妙问题，且无法用索引优化
SELECT * FROM orders WHERE created_at::date BETWEEN '2026-09-01' AND '2026-09-30';

4. 按天/小时聚合统一用 date_trunc

sql
SELECT
  date_trunc('hour', created_at) AS bucket,
  count(*)                       AS cnt
FROM orders
GROUP BY 1
ORDER BY 1;

5. 连接串显式固定 TimeZone 与 DateStyle

text
jdbc:postgresql://host:5432/demo?TimeZone=Asia/Shanghai&DateStyle=ISO

或在数据库/角色级设定：

sql
ALTER DATABASE demo SET TimeZone TO 'Asia/Shanghai';
ALTER ROLE app_user SET DateStyle TO 'ISO, YMD';

6. 需要"最近 N 天"用 now() - INTERVAL '7 days' 而非字符串拼接

sql
SELECT * FROM orders
WHERE created_at >= now() - INTERVAL '7 days';

7. 时间戳列上建索引以支持范围查询

sql
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);

若查询总是"最近的数据"，DESC 索引配合 LIMIT 效果最好，因为顺序扫描即可提前终止。

8. 存储"出生日期"等纯日历信息用 DATE

sql
CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  birth_date DATE,
  CONSTRAINT chk_birth_date CHECK (birth_date < CURRENT_DATE)
);

注意：CHECK 约束中不能使用 now() 等非稳定函数（PostgreSQL 会直接拒绝）：

sql
ALTER TABLE users ADD CONSTRAINT chk_birth CHECK (birth_date < now()::date);
text
ERROR:  functions in check constraint must be marked IMMUTABLE

而 CURRENT_DATE 是特例，PostgreSQL 允许它出现在 CHECK 中，但语义上并不合理——约束只在写入时求值，日期推移后约束条件便会失效。

9. 时间序列数据的表分区用 DATE/TIMESTAMPTZ 做分区键

sql
CREATE TABLE events (
  id         BIGINT GENERATED ALWAYS AS IDENTITY,
  created_at TIMESTAMPTZ NOT NULL,
  payload    JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

10. 交换给外部系统的时间统一用 Unix epoch 或 ISO 8601 带偏移

sql
SELECT
  extract(epoch FROM now())                     AS epoch,
  to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF')   AS iso8601;
text
      epoch      |      iso8601
-----------------+---------------------
 1789967400.123  | 2026-09-22T10:30:00+08

下一节

继续学习：TIMESTAMPTZ 详解

参考链接 ​