将Oracle DATE列迁移到具有时区的TIMESTAMP [英] Migrating Oracle DATE columns to TIMESTAMP with timezone

问题描述

Bakground：
我有一个旧版应用程序，我正在使用DATE类型，用于数据库中的大部分时间存储。我想尝试更新这些表，以便他们可以利用时区，因为这会导致用户在数据库不同区域的问题（请参见下面的 A ）。这是用于Oracle 10g。

Quetions：

1）我可以迁移这个到位。那就是我可以这样转换

  DATE_COL = type：DATE => DATE_COL = type：TIMESTAMP 
  

...或者我必须使用不同的列名？ p>

请记住，需要保留数据。如果这样做可以在迁移脚本中轻松完成，它将适用于我的目的。

2）这种类型的转换是否向后兼容？我们可能会有一些脚本或报告，这将打击我们可能不知道的这个表。我们可以处理它，但我想知道我正在走哪一种大黄蜂巢。

3）我应该注意什么陷阱？谢谢，

编辑：

（部分回应Gary）

p>

我很好，有一个多步骤的过程。 1）将数据移动到一个新的Timestamp列（caled TEMP），以某种转换方式
2）drop old column（我们称之为MY_DATE）
3）创建具有旧日期列名称（MY_DATE）的新时间戳列
4）将数据移动到MY_DATE列
5）删除TEMP列

A Gary还希望澄清具体的时区问题。我从下面复制了我的答案，使其更易于阅读。

基本上，数据将从几个不同的领域进行访问。我们需要能够根据需要转换到/从本地时区。我们也有触发器使用sysdate进一步复杂的事情。时间戳与时区缓解了很多这种痛苦。

哦，感谢您的答案。

解决方案

您可以运行：

  ALTER TABLE your_table MODIFY your_date_column TIMESTAMP WITH TIME ZONE; 
  

但是我建议在表中添加一个TIMESTAMP列，使用UPDATE语句填充，然后删除原始日期列如果您选择：

  ALTER TABLE your_table ADD date_as_timestamp TIMESTAMP WITH TIME ZONE; 
 
更新your_table 
 SET date_as_timestamp = CAST（date_column AS TIMESTAMP WITH TIME ZONE）; 
  

转换向后兼容 - 您可以切换回你喜欢。

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.

Quetions:

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) 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) What pitfalls should I be on the lookout for?

Thanks,

EDIT:
(partly in response to Gary)

I'm fine with a multi-step process.

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 also wanted clarification on the specific timezone issue. I copied my answer from below to keep it more readable.

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.

解决方案

You could just run:

ALTER TABLE your_table MODIFY your_date_column TIMESTAMP WITH TIME ZONE;

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.

这篇关于将Oracle DATE列迁移到具有时区的TIMESTAMP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！