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屋！