Oracle将TIMESTAMP与Timezone转换为DATE [英] Oracle Convert TIMESTAMP with Timezone to DATE
问题描述
我有DB与时区 +04:00(欧洲/莫斯科)
,需要转换格式为 YYYY-MM-DD THH24:MI:SSTZH:TZM
到 DATE
Oracle 11g中的数据类型。
<换句话说,我有一个字符串
2013-11-08T10:11:31 + 02:00
,我想将其转换为 DATE
数据类型(在本地DB时区 +04:00(欧洲/莫斯科)
)。 对于字符串 2013-11-08T10:11:31 + 02:00
我想要的转换应该返回 DATE
数据类型与日期 2013-11-08 12:11:31
(即将本地时区转换为 +04:00(欧洲/莫斯科)
)。字符串的时区可能不同,以上字符串中的 +02:00
只是示例。
我试图做这与 TIMESTAMP
数据类型,但没有成功的时区转换。
p> to_timestamp_tz()
函数与在时区
子句可用于将字符串文字转换为值带有时区的时间戳记
数据类型:
SQL> t1(tm)为(
/ pre>
2从双
3中选择'2013-11-08T10:11:31 + 02:00')
4选择to_timestamp_tz(tm,'yyyy-mm -ddThh24:mi:ss TZH:TZM')
5在时区'+4:00'为this_way
6,to_timestamp_tz(tm,'yyyy-mm-ddT hh24:mi:ss TZH:TZM')
7在时区欧洲/莫斯科为or_this_way
8从t1
9 /
结果:
THIS_WAY OR_THIS_WAY
- -------------------------------------------------- -------------------------
2013-11-08 12.11.31 PM +04:00 2013-11-08 12.11.31 PM EUROPE / MOSCOW
然后,我们使用
cast()
函数产生一个值date
数据类型:与t1(tm)为(
从双
中选择'2013-11-08T10:11:31 + 02:00')
选择转换(to_timestamp_tz(tm,'yyyy-mm -ddThh24:mi:ss TZH:TZM')
在时区'+4:00'作为日期)as this_way
,cast(to_timestamp_tz(tm,'yyyy-mm-ddThh24:mi:ss TZH:TZM')
在时间区域欧洲/莫斯科作为日期)as或_this_way
从t1
This_Way Or_This_Way
------------------- -----------------------
2013-11-08 12:11:31 2013-11-08 12:11:31
了解更多有关在时区子句和 to_timestamp_tz()函数。
I have DB with timezone
+04:00 (Europe/Moscow)
and need to convert a string in formatYYYY-MM-DD"T"HH24:MI:SSTZH:TZM
toDATE
data type in Oracle 11g.In other words, I have a string
2013-11-08T10:11:31+02:00
and I want to convert it toDATE
data type (in local DB timezone+04:00 (Europe/Moscow)
).For string
2013-11-08T10:11:31+02:00
my desired transformation should returnDATE
data type with date2013-11-08 12:11:31
(i.e. with local timezone transformation of time to+04:00 (Europe/Moscow)
). Timezone of string may be different and+02:00
in string above is just example.I tried to do this with
TIMESTAMP
data type, but no success with time zone transformation.解决方案
to_timestamp_tz()
function withat time zone
clause can be used to convert your string literal to a value oftimestamp with time zone
data type:SQL> with t1(tm) as( 2 select '2013-11-08T10:11:31+02:00' from dual 3 ) 4 select to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') 5 at time zone '+4:00' as this_way 6 , to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') 7 at time zone 'Europe/Moscow' as or_this_way 8 from t1 9 /
Result:
THIS_WAY OR_THIS_WAY ---------------------------------------------------------------------------- 2013-11-08 12.11.31 PM +04:00 2013-11-08 12.11.31 PM EUROPE/MOSCOW
And then, we use
cast()
function to produce a value ofdate
data type:with t1(tm) as( select '2013-11-08T10:11:31+02:00' from dual ) select cast(to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') at time zone '+4:00' as date) as this_way , cast(to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') at time zone 'Europe/Moscow' as date) as or_this_way from t1 This_Way Or_This_Way ------------------------------------------ 2013-11-08 12:11:31 2013-11-08 12:11:31
Find out more about at time zone clause and to_timestamp_tz() function.
这篇关于Oracle将TIMESTAMP与Timezone转换为DATE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!