postgresql 将列从时区时间更改为时间戳

alter column from time with time zone to timestamp

提问by wilty

I am having trouble changing a column called end_datein a table called key_requestfrom time with time zone to timestampin my Postgres database . I have tried using the following code:

我在更改从 time with time zone 到我的 Postgres 数据库中调用end_date的表中调用的列时遇到问题。我尝试使用以下代码：key_requesttimestamp

alter table key_request alter column end_date type timestamp with time zone using end_date::timestamp with time zone

I keep getting the following error:

我不断收到以下错误：

ERROR:  cannot cast type time with time zone to timestamp with time zone

Any idea of how I can adjust this query to work?

知道如何调整此查询以使其正常工作吗？

采纳答案by Declan_K

I woul do this in a series of steps

我将通过一系列步骤来做到这一点

1. Alter the table, adding a new column end_date1as time with time zone
2. Copy the date from end_date(old) to end_date1
3. Alter the table, droping the old end_datecolumn
4. Alter the table,reaming end_date1to end_date

1. 更改表，添加一个新列end_date1作为time with time zone
2. 将日期从end_date（旧）复制到end_date1
3. 改变表，删除旧end_date列
4. 修改表，扩孔end_date1到end_date

回答by Roman Pekar

you can do something like this:

你可以做这样的事情：

alter table key_request
alter column end_date type timestamp with time zone using date('20130101') + end_date;

sql fiddle demo

sql fiddle demo

回答by DanielK

Changing from java.sql.Dateto java.util.Date

更改java.sql.Date为java.util.Date

ALTER TABLE key_request ALTER COLUMN end_date TYPE timestamp without time zone;

If you have dependant views that relate to that table

如果您有与该表相关的依赖视图

1. drop views
2. change column type
3. recreate views

1. 删除视图
2. 更改列类型
3. 重新创建视图

Solution coming from a java/hibernate approach while in type migration.

在类型迁移中来自 java/hibernate 方法的解决方案。

• java.sql.Dateis translated to date
• java.util.Dateis translated to timestamp without time zone

• java.sql.Date被翻译成 date
• java.util.Date被翻译成 timestamp without time zone