PostgreSQL创建表时如何为timestamp列指定时区？

你原语句的语法错误在于：timestamp with time zone（PostgreSQL中可简写为timestamptz）类型的列不能在定义时直接绑定固定时区。这个类型的设计逻辑是存储UTC时间戳，查询时会根据当前会话的时区设置自动转换显示。

以下是几种实现“指定时区”需求的正确方式：

1. 插入时自动转换为指定时区的UTC时间 #

如果希望插入数据时，自动将输入的时间按目标时区（如America/Los_Angeles）转换为UTC存储，可以给列设置默认值，或者插入时手动转换：

默认值方式 #

CREATE TABLE t1(
  a date,
  b timestamptz DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles'),
  c timestamp without time zone
);

手动插入转换 #

INSERT INTO t1 (b) VALUES ('2024-05-20 10:00:00' AT TIME ZONE 'America/Los_Angeles');

这条语句会把洛杉矶时区的2024-05-20 10:00:00转换为UTC时间后存储。

2. 用生成列固定显示指定时区的时间 #

如果需要存储UTC时间的同时，始终能直接获取目标时区的本地时间，可以创建生成列：

CREATE TABLE t1(
  a date,
  b timestamptz,
  b_la_time timestamp without time zone GENERATED ALWAYS AS (b AT TIME ZONE 'America/Los_Angeles') STORED,
  c timestamp without time zone
);

b_la_time会自动根据b的UTC时间转换为洛杉矶时区的本地时间，无需手动维护。

3. 存储指定时区的本地时间并加约束 #

如果想直接存储洛杉矶时区的本地时间，且确保输入数据符合该时区规则，可以用timestamp without time zone类型加检查约束：

CREATE TABLE t1(
  a date,
  b timestamp without time zone,
  c timestamp without time zone,
  CONSTRAINT check_b_la_timezone CHECK (b = (b AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'America/Los_Angeles')
);

这种方式需要应用层保证输入的时间确实是洛杉矶时区的，约束仅用于防止错误的时区转换操作。

内容的提问来源于stack exchange，提问作者Tom