SQL 将 Oracle DATE 列迁移到带时区的 TIMESTAMP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1664627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Migrating Oracle DATE columns to TIMESTAMP with timezone
提问by Jason Tholstrup
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 Abelow). This is for Oracle 10g.
Bakground：我有一个我正在开发的遗留应用程序，它使用 DATE 类型来存储数据库中的大部分时间。我想尝试更新其中一些表，以便它们可以利用时区，因为这会导致与数据库所在区域不同的用户出现问题（请参见下面的A）。这是针对 Oracle 10g 的。
Quetions:
问题：
1) Can I migrate this "in place." That is can I convert like so
1）我可以“就地”迁移这个吗？那是我可以像这样转换吗
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.
2) 这种类型的转换是否向后兼容？我们可能有一些我们可能不知道的脚本或报告会出现在这个表中。我们可能会处理它，但我想知道我走进的是哪种马蜂窝。
3) What pitfalls should I be on the lookout for?
3) 我应该注意哪些陷阱？
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
1）通过某种转换将数据移动到新的时间戳列（称为 TEMP） 2）删除旧列（我们称之为 MY_DATE） 3）使用旧日期列名称（MY_DATE）创建新的时间戳列 4）移动数据到 MY_DATE 列 5) 删除 TEMP 列
AGary 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.
基本上，数据将从几个不同的区域访问。我们需要能够根据需要转换到/从本地时区。我们也有使用 sysdate 的触发器使事情进一步复杂化。带时区的时间戳减轻了很多这种痛苦。
Oh and thanks for the answers so far.
哦，谢谢你到目前为止的答案。
回答by OMG Ponies
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:
但我建议在表中添加一个 TIMESTAMP 列，使用 UPDATE 语句来填充，如果您选择删除原始日期列：
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.
转换是向后兼容的 - 您可以根据需要来回切换。
回答by Gary Myers
Simple enough to demonstrate
简单到足以证明
SQL> create table x (y date);
Table created.
SQL> insert into x select sysdate from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table x modify y timestamp;
Table altered.
SQL> select * from x;
Y
---------------------------------------------------------------------------
03/NOV/09 12:49:03.000000 PM
SQL> alter table x modify y date;
Table altered.
SQL> select * from x;
Y
---------
03/NOV/09
SQL> alter table x modify y timestamp with time zone;
alter table x modify y timestamp with time zone
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
SQL> alter table x modify y timestamp with local time zone;
Table altered.
SQL> alter table x modify y date;
Table altered.
So you can go from date to timestamp (or timestamp with local timezone) and back again, but not for timestamp with time zone (ie where the offset is persisted). You'd have to add another column, and copy the existing data over (with a default for the appropriate time zone).
因此，您可以从日期到时间戳（或带有本地时区的时间戳）然后再返回，但不能用于带时区的时间戳（即偏移量持续的位置）。您必须添加另一列，并复制现有数据（使用适当时区的默认值）。
"causing problems with users in different areas from where the db is". Might help to be a bit more specific. Is it sufficient to convert the dates (or timestamps) from the database timezone to the user's timezone when inserted/changed/queried, or do you actually need to persist the fact that the record was created at 3:00pm in a specific timezone.
“导致与数据库所在不同区域的用户出现问题”。可能有助于更具体一点。在插入/更改/查询时将日期（或时间戳）从数据库时区转换为用户的时区是否足够，或者您是否真的需要坚持记录是在特定时区下午 3:00 创建的事实。
