Oracle：为列设置时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2498715/
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
Oracle: set timezone for column
提问by dbf
I need to do migration date->timestamp with timezone similar to described here: Migrating Oracle DATE columns to TIMESTAMP with timezone. But I need to make additional convertion (needed to work correctly with legacy apps): for all dates we need to change timezone to UTC and set time to 12:00 PM. So now dates are stored in local database (New York) timezone. I need to convert them this way
我需要使用时区进行迁移 date->timestamp ，类似于此处所述：Migrating Oracle DATE columns to TIMESTAMP with timezone。但我需要进行额外的转换（需要与遗留应用程序正常工作）：对于所有日期，我们需要将时区更改为 UTC 并将时间设置为下午 12:00。所以现在日期存储在本地数据库（纽约）时区中。我需要以这种方式转换它们
25/12/2009 09:12 AM (local timezone) in date column => 25/12/2009 12:00 PM UTC timestamp with local timezone column.
25/12/2009 09:12 AM（本地时区）在日期列 => 25/12/2009 12:00 PM UTC 时间戳与本地时区列。
Could you advice, how to set timezone for date value in Oracle (I found only suggestions how to convert from one timezone to another) (for example in Java there is setTimeZone method for Calendar objects).
您能否建议一下，如何在 Oracle 中为日期值设置时区（我只找到了如何从一个时区转换为另一个时区的建议）（例如，在 Java 中有 Calendar 对象的 setTimeZone 方法）。
We want to make a convertion this way:
我们想以这种方式进行转换：
- rename old date column to NAME_BAK
- create new column timestamp with local timezone
- iterate over old column for not-null values set timezone to UTC, time to 12:00 PM
- drop old column after testing of this migration
- 将旧日期列重命名为 NAME_BAK
- 使用本地时区创建新的列时间戳
- 迭代旧列的非空值将时区设置为 UTC，时间为下午 12:00
- 测试此迁移后删除旧列
采纳答案by APC
Creating and populating the new column ...
创建并填充新列...
SQL> alter table t23
2 add new_col timestamp(3) with time zone
3 /
Table altered.
SQL> update t23
2 set new_col = col3
3 /
7 rows updated.
SQL> select to_char(new_col,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') new_col
2 from t23
3 /
NEW_COL
----------------------------
22-MAR-2010 03:20:58.000 PST
21-MAR-2010 03:20:58.000 PST
20-MAR-2010 03:20:58.000 PST
19-MAR-2010 03:20:58.000 PST
18-MAR-2010 03:20:58.000 PST
17-MAR-2010 03:20:58.000 PST
16-MAR-2010 03:20:58.000 PST
7 rows selected.
SQL>
So now to set the values of COL3 to NOON UTC, or GMT as we Brits (and Oracle) know it:
所以现在将 COL3 的值设置为 NOON UTC，或我们英国人（和 Oracle）所知道的 GMT：
SQL> alter session set time_zone = 'GMT'
2 /
Session altered.
SQL> update t23
2 set col3 =
3 cast(to_char(col3, 'DD-MON-YYYY')||'12:00:00' as timestamp) at time zone sessiontimezone
4 /
7 rows updated.
SQL>
Let's see the outcome:
让我们看看结果：
SQL> alter session set time_zone = 'PST'
2 /
Session altered.
SQL> select to_char(new_col,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') as orig_val
2 , to_char(col3,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') as upd_val
3 from t23
4 /
ORIG_VAL UPD_VAL
---------------------------- ----------------------------
22-MAR-2010 03:20:58.000 PST 22-MAR-2010 12:00:00.000 GMT
21-MAR-2010 03:20:58.000 PST 21-MAR-2010 12:00:00.000 GMT
20-MAR-2010 03:20:58.000 PST 20-MAR-2010 12:00:00.000 GMT
19-MAR-2010 03:20:58.000 PST 19-MAR-2010 12:00:00.000 GMT
18-MAR-2010 03:20:58.000 PST 18-MAR-2010 12:00:00.000 GMT
17-MAR-2010 03:20:58.000 PST 17-MAR-2010 12:00:00.000 GMT
16-MAR-2010 03:20:58.000 PST 16-MAR-2010 12:00:00.000 GMT
7 rows selected.
SQL>
All that's left to do is to drop the backup column...
剩下要做的就是删除备份列...
SQL> alter table t23 drop column new_col
2 /
Table altered.
SQL>
Although, if it is a big table you may prefer to set it to UNUSED and then drop it in slow time.
但是，如果它是一张大桌子，您可能更愿意将其设置为 UNUSED，然后慢慢放下。
回答by Vincent Malgrat
You might want to read the documentation about Oracle datatypes:
您可能想阅读有关 Oracle 数据类型的文档：
- a DATEcolumn doesn't have a timezone, this information simply isn't stored with this datatype.
- a TIMESTAMP WITH LOCAL TIME ZONEcolumn has the same time zone offset as the database. You can't specify a different offset for a column of this type.
- 一个DATE列没有一个时区，该信息根本没有存储与此数据类型。
- 一个TIMESTAMP WITH LOCAL TIME ZONE列具有相同的时区数据库偏移。您不能为这种类型的列指定不同的偏移量。
It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.
它与 TIMESTAMP WITH TIME ZONE 的不同之处在于，存储在数据库中的数据被规范化为数据库时区，并且时区偏移量不作为列数据的一部分存储。当用户检索数据时，Oracle 在用户的本地会话时区返回数据。时区偏移量是本地时间和 UTC（协调世界时 - 以前的格林威治标准时间）之间的差异（以小时和分钟为单位）。此数据类型对于在两层应用程序中以客户端系统的时区显示日期信息很有用。
If you want to store the time zone you will have to use the datatype TIMESTAMP WITH TIME ZONE.
如果要存储时区，则必须使用数据类型TIMESTAMP WITH TIME ZONE。
You would convert a date to a timestamp with the to_timestamp_tzfunction for example:
您可以使用to_timestamp_tz函数将日期转换为时间戳，例如：
SQL> WITH DATA AS (
2 SELECT to_date('25/12/2009 09:12 AM', 'DD/MM/YYYY HH:MI AM') dd FROM dual
3 )
4 SELECT dd,
5 to_timestamp_tz(to_char(dd, 'YYYYMMDD')||' GMT', 'YYYYMMDD TZR') tz
6 FROM DATA;
DD TZ
----------- -------------------------------------------------
25/12/2009 25/12/09 00:00:00,000000000 GMT
