Oracle时区转换(使用from_tz) [英] Oracle Time Zones Conversion (using from_tz)
问题描述
我正在尝试将一个时区(日期+时间)从一个时区转换到另一个时区。在下面的查询中,我正在尝试将EST(America / New_York)的时间转换为PST(America / Los_Angeles)。查询部分工作;结果:
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:
DATABASE_DATE = 2012-02-13 1:00:00 PM
LOCALTIME (what I get): 2012-02-12 10:00:00 AM.
所以时间不错,但日期错误。应该是2012-02-13而不是2012-02-12。
So the time is good but the date is wrong. It should be 2012-02-13 instead of 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
谢谢
推荐答案
to_timestamp()获取一个字符串(VARCHAR2,CHAR ...),如果你试图给它一个日期,那么oracle会根据NLS_DATE_FORMAT将它转换成一个字符串,它可能在不同的环境中有所不同,并返回意想不到的结果(在这种情况下)。
你应该做的是先使用to_char,这样你的查询可以看起来像这样:
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:
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
这篇关于Oracle时区转换(使用from_tz)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!