I found this ALTER COLUMN
statement in the PostgreSQL 9.3 ALTER TABLE
manual page:
我在PostgreSQL 9.3 ALTER TABLE手册页中找到了这个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';
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.
The ALTER TABLE
statement 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语句看起来非常直接。但我已经尝试了各种类型的转换和转换列类型,但无法使其工作。我错过了什么?
2 个解决方案
#1
16
The example in the Postgres manual (as well as the working fiddle by @mvp) transform an integer
column (representing a UNIX epoch) to timestamptz
.
Postgres手册中的示例(以及@mvp的工作小提琴)将整数列(表示UNIX纪元)转换为timestamptz。
The error message as well as your title clearly indicate you are trying to convert a timestamp
to timestamptz
. And this just works automatically, without explicit cast.
错误消息以及您的标题清楚地表明您正在尝试将时间戳转换为timestamptz。这只是自动工作,没有明确的演员。
ALTER TABLE test ALTER created_at TYPE timestamptz;
More about timestamp vs. timestamptz:
Ignoring timezones altogether in Rails and PostgreSQL
有关timestamp与timestamptz的更多信息:在Rails和PostgreSQL中完全忽略时区
timestamp
values are always interpreted according to the time zone setting of your session. To assume the time zone UTC for the conversion:
时间戳值始终根据会话的时区设置进行解释。假设转换的时区UTC:
BEGIN;
SET LOCAL timezone='UTC';
ALTER TABLE test ALTER created_at TYPE timestamptz;
COMMIT;
Or use the AT TIME ZONE
construct:
或使用AT TIME ZONE构造:
ALTER TABLE test ALTER created_at TYPE timestamptz
USING created_at AT TIME ZONE 'UTC';
You can assume any time zone this way.
您可以通过这种方式假设任何时区。
#2
0
It works fine for me: SQLFiddle.
它适用于我:SQLFiddle。
I think what you have is your SQL client caching data types. I had this issue with mine until I restarted it, and type was indeed changed fine.
我认为你拥有的是SQL客户端缓存数据类型。我有这个问题,直到我重新启动它,并且类型确实改变了。
#1
16
The example in the Postgres manual (as well as the working fiddle by @mvp) transform an integer
column (representing a UNIX epoch) to timestamptz
.
Postgres手册中的示例(以及@mvp的工作小提琴)将整数列(表示UNIX纪元)转换为timestamptz。
The error message as well as your title clearly indicate you are trying to convert a timestamp
to timestamptz
. And this just works automatically, without explicit cast.
错误消息以及您的标题清楚地表明您正在尝试将时间戳转换为timestamptz。这只是自动工作,没有明确的演员。
ALTER TABLE test ALTER created_at TYPE timestamptz;
More about timestamp vs. timestamptz:
Ignoring timezones altogether in Rails and PostgreSQL
有关timestamp与timestamptz的更多信息:在Rails和PostgreSQL中完全忽略时区
timestamp
values are always interpreted according to the time zone setting of your session. To assume the time zone UTC for the conversion:
时间戳值始终根据会话的时区设置进行解释。假设转换的时区UTC:
BEGIN;
SET LOCAL timezone='UTC';
ALTER TABLE test ALTER created_at TYPE timestamptz;
COMMIT;
Or use the AT TIME ZONE
construct:
或使用AT TIME ZONE构造:
ALTER TABLE test ALTER created_at TYPE timestamptz
USING created_at AT TIME ZONE 'UTC';
You can assume any time zone this way.
您可以通过这种方式假设任何时区。
#2
0
It works fine for me: SQLFiddle.
它适用于我:SQLFiddle。
I think what you have is your SQL client caching data types. I had this issue with mine until I restarted it, and type was indeed changed fine.
我认为你拥有的是SQL客户端缓存数据类型。我有这个问题,直到我重新启动它,并且类型确实改变了。