我想将一列数据从文本更改为类型时间戳.我的数据中没有时区.我的数据格式如28-03-17 17:22,包括时间和日期,但没有时区.换句话说,我的所有数据都在同一时区.我该怎么做？
我在下面尝试了多种方法,但我仍然找不到合适的方法.希望您能够帮助我.
当然,如果我的麻烦可以解决,我可以建立一个新的表.
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且具有有效的日期值,则更容易按如下方式继续进行更改(建议首先将表转储作为备份)：
-- 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;
