postgreSQL将列数据类型更改为没有时区的时间戳 [英] postgreSQL alter column data type to timestamp without time zone
问题描述
我想将一列数据从文本更改为时间戳类型。我的数据中没有时区。我的数据格式类似于28-03-17 17:22,包括时间和日期,但没有时区。换句话说,我所有的数据都在同一时区。我该怎么做?
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?
我在下面尝试了多种方法,但仍然找不到正确的方法。希望您能帮助我。
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
推荐答案
如果 create_time
为TEXT类型且具有有效的日期值,则按以下步骤进行更改会更容易(建议先进行表转储作为备份) ):
If create_time
is 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 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;
这篇关于postgreSQL将列数据类型更改为没有时区的时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!