oracle 将“带时区的时间戳”列字段转换为服务器的当前时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3612654/
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
Convert a "timestamp with time zone" column field to the current timezone of the server
提问by user305801
In Oracle how do I convert a "timestamp with time zone" to the current time zone of the server? I need to dynamically figure out what the current time zone of the server is.
在 Oracle 中，如何将“带时区的时间戳”转换为服务器的当前时区？我需要动态找出服务器的当前时区是什么。
create table test_table1
(rec1 timestamp with time zone)
insert into test_table1(rec1) values (
to_timestamp_tz('1/1/1900 09:00:00 AM US/EASTERN','dd-mm-yyyy hh:mi:ss AM TZR')
)
SELECT NEW_TIME(rec1, TO_CHAR(rec1, 'TZR'), TO_CHAR(SYSTIMESTAMP, 'TZR'))
FROM test_table1
The example above creates a table with the time zone column. Then I insert a row into the table with the eastern timezone. The select statement doesn't work. What I am trying to achieve is to convert the rec_1 column to the timezone of the server and return that.
上面的示例创建了一个包含时区列的表。然后我在表中插入一行东部时区。选择语句不起作用。我想要实现的是将 rec_1 列转换为服务器的时区并返回。
回答by Gary Myers
You can get the current system timezone [in some format or other] through :
您可以通过以下方式获取当前系统时区 [以某种格式或其他格式]：
select to_char(systimestamp,'TZD | TZH TZM [ TZR ]'), DBTIMEZONE
from dual;
The following may help with conversion. Not sure whether your session timezone is the same as your database timezone.
以下内容可能有助于转换。不确定您的会话时区是否与您的数据库时区相同。
SELECT rec1, SYS_EXTRACT_UTC(rec1), cast(rec1 as timestamp with local time zone)
FROM test_table1
when I use systeimestamp I get null
当我使用系统时间戳我得到空
TZD can return null (eg it knows what the offset it, but hasn't got an 'alias' for it). TZR shouldn't. Maybe something like...
TZD 可以返回空值（例如，它知道它的偏移量是多少，但没有得到它的“别名”）。TZR 不应该。也许像...
SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', to_char(systimestamp,'TZR'))
FROM DUAL;
回答by user3407098
Oracle will convert time zones based on Standard and Daylight Saving Time if regions "TZR" are used instead of Hours and minutes "TZH:TZM" offset.
如果使用区域“TZR”而不是小时和分钟“TZH:TZM”偏移量，Oracle 将根据标准和夏令时转换时区。
To qualify your region you can use (in 11g not sure of other versions)
要符合您可以使用的区域的资格（在 11g 中不确定其他版本）
SELECT SYSTIMESTAMP AT TIME ZONE 'US/Eastern' FROM DUAL;
The region used must be stored in the DB which you can check by
使用的区域必须存储在您可以检查的数据库中
SELECT * FROM v$timezone_names WHERE tzname LIKE 'US%';
This query can be modified to identify any region.
可以修改此查询以识别任何区域。
The Oracle Globalization Support manual is also a good reference.
Oracle Globalization Support 手册也是一个很好的参考。
