将 Oracle DATE 列迁移到带有时区的 TIMESTAMP
背景:我有一个正在开发的遗留应用程序，它使用 DATE 类型来存储数据库中的大部分时间.我想尝试更新其中一些表，以便他们可以利用时区，因为这会导致数据库所在不同区域的用户出现问题(请参阅下面的 A).这是针对 Oracle 10g 的.
Bakground: I've got a legacy app I'm working on that uses DATE types for most time storage in the database. I'd like to try update some of these tables so that they can utilize time zones since this is causing problems with users in different areas from where the db is(see A below). This is for Oracle 10g.
问题:
1) 我可以就地"迁移它吗?那我可以这样转换吗
1) Can I migrate this "in place." That is can I convert like so
DATE_COL = type:DATE => DATE_COL = type:TIMESTAMP
...还是我必须使用不同的列名?
...or will I have to use a different column name?
请记住，需要保留数据.如果这可以在迁移脚本中轻松完成，那么它将适用于我的目的.
Keep in mind that data needs to be retained. If this can be done semi-easily in a migration script it will work for my purposes.
2) 这种类型的转换会向后兼容吗?我们可能有一些我们可能不知道的脚本或报告会出现在此表中.我们可能可以处理它，但我想知道我正在走进什么样的马蜂窝.
2) Will this type of conversion be backwards compatible? We likely have some scripts or reports that will hit this table that we may not know about. We can probably deal with it but I'd like to know what sort of hornet's nest I'm walking into.
3) 我应该注意哪些陷阱?
3) What pitfalls should I be on the lookout for?
谢谢，
(部分回应加里)
(partly in response to Gary)
我可以接受多步骤流程.
I'm fine with a multi-step process.
1) 通过某种转换将数据移动到新的时间戳列(称为 TEMP)2) 删除旧列(我们称之为 MY_DATE)3) 使用旧日期列名 (MY_DATE) 创建新的时间戳列4) 将数据移动到 MY_DATE 列5) 删除 TEMP 列
1) move data to a new Timestamp column (caled TEMP) with some sort of conversion 2) drop old column (we'll call it MY_DATE) 3) create new timestamp column with the old date column name (MY_DATE) 4) move data to the MY_DATE column 5) drop TEMP column
A Gary 还想澄清具体的时区问题.我从下面复制了我的答案以使其更具可读性.
A Gary also wanted clarification on the specific timezone issue. I copied my answer from below to keep it more readable.
基本上，数据将从几个不同的区域访问.我们需要能够根据需要转换到本地时区/从本地时区转换.我们还有使用 sysdate 的触发器使事情变得更加复杂.带有时区的时间戳减轻了很多这种痛苦.
Basically the data will be accessed from several different areas. We need to be able to convert to/from the local time zone as needed. We also have triggers that use sysdate further complicating things. timestamp with time zone alleviates a lot of this pain.
哦，谢谢你到目前为止的回答.
Oh and thanks for the answers so far.
推荐答案
你可以直接运行:
ALTER TABLE your_table MODIFY your_date_column TIMESTAMP WITH TIME ZONE;
但我建议在表中添加一个 TIMESTAMP 列，使用 UPDATE 语句填充，如果您选择删除原始日期列:
But I would recommend adding a TIMESTAMP column to the table, using an UPDATE statement to populate, and drop the original date column if you so choose:
ALTER TABLE your_table ADD date_as_timestamp TIMESTAMP WITH TIME ZONE;
UPDATE your_table
SET date_as_timestamp = CAST(date_column AS TIMESTAMP WITH TIME ZONE);
转换是向后兼容的 - 您可以切换回来 &随心所欲.
The conversion is backwards compatible - you can switch back & forth as you like.
相关文章