SQL postgreSQL 将列数据类型更改为没有时区的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43059108/
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
postgreSQL alter column data type to timestamp without time zone
提问by Amy
I want to alter one column of data from text into type timestamp. There is no time zone in my data. The format of my data is like 28-03-17 17:22, including time and date but no time zone. In other words, all my data are in the same time zone. How can I do it?
我想将一列数据从文本更改为时间戳类型。我的数据中没有时区。我的数据格式是28-03-17 17:22，有时间和日期，没有时区。换句话说，我所有的数据都在同一个时区。我该怎么做？
I tried multiple ways below, but I still can not find the right approach. Hope you can help me.
我在下面尝试了多种方法，但仍然找不到正确的方法。希望您能够帮助我。
Certainly, I can build a new table if my trouble can be solved.
当然，如果我的问题可以解决，我可以建一个新表。
alter table AB
alter create_time type TIMESTAMP;
ERROR: column "create_time" cannot be cast automatically to type timestamp without time zone
HINT: You might need to specify "USING create_time::timestamp without time zone".
********** Error **********
ERROR: column "create_time" cannot be cast automatically to type timestamp without time zone
SQL state: 42804
Hint: You might need to specify "USING create_time::timestamp without time zone".
alter table AB
alter create_time type TIMESTAMP without time zone;
ERROR: column "create_time" cannot be cast automatically to type timestamp without time zone
HINT: You might need to specify "USING create_time::timestamp without time zone".
********** Error **********
ERROR: column "create_time" cannot be cast automatically to type timestamp without time zone
SQL state: 42804
Hint: You might need to specify "USING create_time::timestamp without time zone".
alter table AB
alter create_time::without time zone type TIMESTAMP;
ERROR: syntax error at or near "::"
LINE 2: alter create_time::without time zone type TIMESTAM
^
********** Error **********
ERROR: syntax error at or near "::"
SQL state: 42601
Character: 50
alter table AB
alter create_time UTC type TIMESTAMP;
ERROR: syntax error at or near "UTC"
LINE 2: alter create_time UTC type TIMESTAMP;
^
********** Error **********
ERROR: syntax error at or near "UTC"
SQL state: 42601
Character: 50
回答by Leo C
If create_timeis of type TEXT with valid date value, it'll be easier to proceed with the change as follows (Be advised to first do a table dump as a backup):
如果create_time是具有有效日期值的 TEXT 类型，则按以下方式进行更改会更容易（建议首先进行表转储作为备份）：
-- Create a temporary TIMESTAMP column
ALTER TABLE AB ADD COLUMN create_time_holder TIMESTAMP without time zone NULL;
-- Copy casted value over to the temporary column
UPDATE AB SET create_time_holder = create_time::TIMESTAMP;
-- Modify original column using the temporary column
ALTER TABLE AB ALTER COLUMN create_time TYPE TIMESTAMP without time zone USING create_time_holder;
-- Drop the temporary column (after examining altered column values)
ALTER TABLE AB DROP COLUMN create_time_holder;
回答by Jasen
USING...comes after the type:
USING...出现在类型之后：
... alter create_time type TIMESTAMP USING create_time::TIMESTAMP;
回答by Leo C
You didn't specify the original type of create_time, so I assume it's TIME with time zone (as type DATE or TIMESTAMP with time zone shouldn't give the said error when trying to alter to TIMESTAMP without time zone). Since TIMESTAMP has date information in addition to TIME, you'll need to supplement your date information in your ALTER statement, like:
您没有指定 create_time 的原始类型，因此我假设它是带时区的 TIME（因为在尝试更改为不带时区的 TIMESTAMP 时，带时区的 DATE 或 TIMESTAMP 类型不应给出上述错误）。由于 TIMESTAMP 除了 TIME 之外还有日期信息，因此您需要在 ALTER 语句中补充您的日期信息，例如：
ALTER TABLE AB ALTER COLUMN create_time TYPE TIMESTAMP without time zone USING date('20170327') + create_time;
If you have a corresponding DATE column (say, create_date), you can pass it to the date() function, like:
如果您有相应的 DATE 列（例如 create_date），您可以将其传递给 date() 函数，例如：
ALTER TABLE AB ALTER COLUMN create_time TYPE TIMESTAMP without time zone USING date(create_date) + create_time;
回答by raman.pndy
for changing the data type of column from bigint to timestamp(for epoch to timestamp)
用于将列的数据类型从 bigint 更改为时间戳（用于 epoch 到时间戳）
alter table <tablename> alter column <columnname> type timestamp without time zone using to_timestamp(<columnname>) AT TIME ZONE 'UTC';
for eg;
例如；
alter table AB alter column col type timestamp without time zone using to_timestamp(col) AT TIME ZONE 'UTC';
for changing the data type of column from timestamp to bigint(for timestamp to epoch)
用于将列的数据类型从时间戳更改为 bigint（将时间戳更改为纪元）
alter table <tablename> alter column <columnname> type bigint using extract(EPOCH from <columnname>);
for eg;
例如；
alter table AB alter column col type bigint using extract(EPOCH from col);
