Oracle 时区转换（使用 from_tz）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9264350/
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 Time Zones Conversion (using from_tz)
提问by Mark Marina
I'm trying to convert a time (date + time) from one time zone to another. In the query below, I'm trying to convert a time from EST ("America/New_York") to PST ("America/Los_Angeles"). The query is partially working; the results:
我正在尝试将时间（日期 + 时间）从一个时区转换为另一个时区。在下面的查询中，我试图将时间从 EST（“America/New_York”）转换为 PST（“America/Los_Angeles”）。查询部分有效；结果：
DATABASE_DATE = 2012-02-13 1:00:00 PM
LOCALTIME (what I get): 2012-02-12 10:00:00 AM.
So the time is good but the date is wrong. It should be 2012-02-13 instead of 2012-02-12.
所以时间是好的，但日期是错误的。它应该是 2012-02-13 而不是 2012-02-12。
Am I doing something wrong? Here's my query:
难道我做错了什么？这是我的查询：
select to_date( to_char( ( from_tz( to_timestamp( DATABASE_DATE
, 'YYYY-MM-DD HH:MI:SS')
,'America/New_York')
at time zone 'America/Los_Angeles')
,'YYYY-MM-DD HH:MI:SS')
,'YYYY-MM-DD HH:MI:SS') as localtime
from table
Thanks
谢谢
采纳答案by A.B.Cade
to_timestamp() gets a string (VARCHAR2, CHAR ...) if you try to give it a date, then oracle will convert it to a string according to NLS_DATE_FORMAT which might vary in different environments and return unexpected results (as in this case).
What you should do is use to_char first, so your query can look like this:
to_timestamp() 得到一个字符串 (VARCHAR2, CHAR ...) 如果你试图给它一个日期，那么 oracle 会根据 NLS_DATE_FORMAT 将它转换成一个字符串，这在不同的环境中可能会有所不同并返回意外的结果（如本例） .
您应该先使用 to_char，这样您的查询就可以如下所示：
select to_date(to_char((from_tz(to_timestamp(to_char(DATABASE_DATE, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'America/New_York')
at time zone 'America/Los_Angeles'),'YYYY-MM-DD HH:MI:SS PM'),'YYYY-MM-DD HH:MI:SS PM') as localtime
from table
UPDATE:if I understand you right then you want something like this:
更新：如果我理解你，那么你想要这样的东西：
select to_char((from_tz(to_timestamp(to_char(DATABASE_DATE, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'America/New_York')
at time zone 'America/Los_Angeles'),'YYYY-MM-DD HH:MI:SS PM TZD') as localtime
from table
回答by grokster
SELECT TO_CHAR(NEW_TIME(systimestamp,'EST','PST'), 'DD-MON-YY HH24:MI:SS') AS converted_timestamp_column FROM DUAL;
回答by user666
Please try this as well .There is a function for this purpose in oracle
请也试试这个。oracle中有一个用于此目的的功能
https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2036.htm
https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2036.htm
select NEW_TIME (TO_DATE ('2011/11/11 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') from dual;
