Oracle 将带有时区的 TIMESTAMP 转换为 DATE

2022-01-13 00:00:00 timestamp sql date oracle

我有带时区 +04:00 (Europe/Moscow) 的数据库，需要转换格式为 YYYY-MM-DD"T"HH24:MI:SSTZH:TZM 的字符串Oracle 11g 中的 到 DATE 数据类型.

I have DB with timezone +04:00 (Europe/Moscow) and need to convert a string in format YYYY-MM-DD"T"HH24:MI:SSTZH:TZM to DATE data type in Oracle 11g.

换句话说，我有一个字符串 2013-11-08T10:11:31+02:00 我想将它转换为 DATE 数据类型(在当地 DB 时区 +04:00 (Europe/Moscow)).

In other words, I have a string 2013-11-08T10:11:31+02:00 and I want to convert it to DATE data type (in local DB timezone +04:00 (Europe/Moscow)).

对于字符串 2013-11-08T10:11:31+02:00 我想要的转换应该返回 DATE 数据类型与日期 2013-11-0812:11:31(即本地时区时间转换为+04:00 (Europe/Moscow)).字符串的时区可能不同，上面字符串中的 +02:00 只是示例.

For string 2013-11-08T10:11:31+02:00 my desired transformation should return DATE data type with date 2013-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.

我尝试使用 TIMESTAMP 数据类型执行此操作，但时区转换没有成功.

I tried to do this with TIMESTAMP data type, but no success with time zone transformation.

推荐答案

to_timestamp_tz() 带有 at time zone 子句的函数可用于将字符串文字转换为timestamp with time zone 数据类型的值:

to_timestamp_tz() function with at time zone clause can be used to convert your string literal to a value of timestamp 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  /

结果:

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 数据类型的值:

And then, we use cast() function to produce a value of date 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

详细了解在时区子句和to_timestamp_tz() 函数.

Find out more about at time zone clause and to_timestamp_tz() function.

相关文章