postgresql 将列类型从没有时区的时间戳更改为有时区的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20793572/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change column type from timestamp WITHOUT time zone to timestamp WITH time zone
提问by dsmorey
I found this ALTER COLUMNstatement in the PostgreSQL 9.3 ALTER TABLEmanual page:
我ALTER COLUMN在 PostgreSQL 9.3ALTER TABLE手册页中找到了这个语句：
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';
I cannot seem to get this to work. I'm getting this error:
我似乎无法让它发挥作用。我收到此错误：
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.
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.
The ALTER TABLEstatement looks very straight-foward. But I've tried all kinds of casts and converting column-types, but can not get this to work. What am I missing?
该ALTER TABLE声明看起来非常直接。但是我尝试了各种类型的转换和转换列类型，但无法使其正常工作。我错过了什么？
回答by Erwin Brandstetter
The example in the Postgres manual (as well as the working fiddle by @mvp) transform an integercolumn (representing a UNIX epoch) to timestamptz.
Postgres 手册中的示例（以及@mvp 的工作小提琴）将一integer列（代表 UNIX 时代）转换为timestamptz.
The error message as well as your title clearly indicate you are trying to convert a timestampto timestamptz. And this just works automatically, without explicit cast.
错误信息以及您的标题清楚地表明你正在尝试将转换timestamp到timestamptz。这只是自动工作，无需显式转换。
ALTER TABLE test ALTER created_at TYPE timestamptz;
More about timestamp vs. timestamptz:
Ignoring timezones altogether in Rails and PostgreSQL
有关时间戳与时间戳的更多信息：
在 Rails 和 PostgreSQL 中完全忽略时区
timestampvalues are always interpreted according to the time zone setting of your session. To assume the time zone UTC for the conversion:
timestamp值始终根据会话的时区设置进行解释。假设转换的时区为 UTC：
BEGIN;
SET LOCAL timezone='UTC';
ALTER TABLE test ALTER created_at TYPE timestamptz;
COMMIT;
Or use the AT TIME ZONEconstruct:
或使用AT TIME ZONE构造：
ALTER TABLE test ALTER created_at TYPE timestamptz
USING created_at AT TIME ZONE 'UTC';
You can assume anytime zone this way.
您可以通过这种方式假设任何时区。
