sql - 将列类型从 timestamp WITHOUT time zone 更改为 timestamp WITH time zone

Question

我在 PostgreSQL 9.3 ALTER TABLE manual page 中找到了这个 ALTER COLUMN 语句:

ALTER TABLE audits
    ALTER COLUMN created_at SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + created_at * interval '1 second';

我似乎无法让它工作。我收到此错误:

ERROR: operator does not exist: timestamp without time zone * interval
SQL state: 42883
Hint: No operator matches the given name and argument type(s).
You might need to add explicit type casts.

ALTER TABLE 语句看起来非常直接。但是我已经尝试了各种类型的转换和转换列类型，但无法使其正常工作。我错过了什么？

Answer 1

Postgres 手册中的示例(以及@mvp 的工作 fiddle )将 integer 列(代表 UNIX 纪元)转换为 timestamptz.

错误消息以及您的标题清楚地表明您正在尝试转换 timestamp到 timestamptz。这只是自动工作，无需显式转换。

ALTER TABLE test ALTER created_at TYPE timestamptz;

-> SQLfiddle.

有关时间戳与 timestamptz 的更多信息:
Ignoring timezones altogether in Rails and PostgreSQL

timestamp 值始终根据 session 的时区设置进行解释。为转换假定时区 UTC:

BEGIN;
SET LOCAL timezone='UTC';
ALTER TABLE test ALTER created_at TYPE timestamptz;
COMMIT;

或者使用 AT TIME ZONE构造:

ALTER TABLE test ALTER created_at TYPE timestamptz
USING created_at AT TIME ZONE 'UTC';

您可以通过这种方式假设任何时区。