postgreSQL将列数据类型更改为没有时区的时间戳

发布时间：2020-12-13 15:58:59 所属栏目：百科来源：网络整理

导读：我想将一列数据从文本更改为类型时间戳.我的数据中没有时区.我的数据格式如28-03-17 17:22,包括时间和日期,但没有时区.换句话说,我的所有数据都在同一时区.我该怎么做？我在下面尝试了多种方法,但我仍然找不到合适的方法.希望您能够帮助我. 当然,如果我的麻

我想将一列数据从文本更改为类型时间戳.我的数据中没有时区.我的数据格式如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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!