通过生成列将TIMESTAMPTZ拆分为DATE和TIME字段

现有如下表结构：

CREATE TABLE "foo" (
    -- ...
    "startTimestamp" TIMESTAMPTZ NOT NULL,
    -- ...
)

需要将该时间戳拆分为单独的DATE和TIME列存储，但不想修改访问该表的客户端代码，尝试用生成列实现：

CREATE TABLE "foo" (
    -- ...
    "startTimestamp" TIMESTAMPTZ NOT NULL,
    "startDate" DATE NOT NULL ALWAYS GENERATED AS ("startTimestamp"::DATE),
    "startTime" TIME NOT NULL ALWAYS GENERATED AS ("startTimestamp"::TIME),
    -- ...
)

执行时触发错误：generation expression is not immutable，原因是TIMESTAMPTZ转DATE/TIME的转换依赖会话本地时区设置，这类转换逻辑不属于immutable函数，不符合生成列的要求。

要让生成列的表达式满足immutable要求，核心是固定时区转换逻辑，消除对会话时区的依赖，以下是两种可行方案：

方案1：指定固定时区完成转换 #

明确指定一个业务认可的固定时区（比如UTC或业务所在时区），先将TIMESTAMPTZ转换为该时区下的TIMESTAMP，再转成DATE和TIME，此时转换逻辑固定，表达式会被判定为immutable：

CREATE TABLE "foo" (
    -- ...
    "startTimestamp" TIMESTAMPTZ NOT NULL,
    "startDate" DATE NOT NULL ALWAYS GENERATED AS (("startTimestamp" AT TIME ZONE 'UTC')::DATE),
    "startTime" TIME NOT NULL ALWAYS GENERATED AS (("startTimestamp" AT TIME ZONE 'UTC')::TIME),
    -- ...
)

可将'UTC'替换为实际业务使用的时区（例如'Asia/Shanghai'），确保所有转换基于同一固定时区。

方案2：自定义immutable转换函数 #

如果需要在多个场景复用转换逻辑，可以创建自定义的immutable函数：

-- 转换为UTC日期的函数
CREATE OR REPLACE FUNCTION timestamptz_to_utc_date(tz TIMESTAMPTZ) 
RETURNS DATE 
LANGUAGE sql 
IMMUTABLE AS $$
    SELECT (tz AT TIME ZONE 'UTC')::DATE;
$$;

-- 转换为UTC时间的函数
CREATE OR REPLACE FUNCTION timestamptz_to_utc_time(tz TIMESTAMPTZ) 
RETURNS TIME 
LANGUAGE sql 
IMMUTABLE AS $$
    SELECT (tz AT TIME ZONE 'UTC')::TIME;
$$;

-- 使用自定义函数创建生成列
CREATE TABLE "foo" (
    -- ...
    "startTimestamp" TIMESTAMPTZ NOT NULL,
    "startDate" DATE NOT NULL ALWAYS GENERATED AS (timestamptz_to_utc_date("startTimestamp")),
    "startTime" TIME NOT NULL ALWAYS GENERATED AS (timestamptz_to_utc_time("startTimestamp")),
    -- ...
)

这种方式灵活性更高，适合多表复用转换逻辑的场景。

关键注意点 #

• 务必选择业务逻辑认可的固定时区，避免因时区不一致导致数据偏差。
• 生成列使用的ALWAYS关键字确保客户端无法直接修改startDate和startTime，只能通过更新startTimestamp间接修改，完全满足无需修改客户端代码的需求。

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