SQL Oracle 将带时区的 TIMESTAMP 转换为 DATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20089859/
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 Convert TIMESTAMP with Timezone to DATE
提问by Nikita Petrov
I have DB with timezone +04:00 (Europe/Moscow)and need to convert a string in format YYYY-MM-DD"T"HH24:MI:SSTZH:TZMto DATEdata type in Oracle 11g.
我有DB与时区+04:00 (Europe/Moscow)，需要一个字符串格式转换YYYY-MM-DD"T"HH24:MI:SSTZH:TZM到DATE数据类型在Oracle 11g中。
In other words, I have a string 2013-11-08T10:11:31+02:00and I want to convert it to DATEdata type (in local DB timezone +04:00 (Europe/Moscow)).
换句话说，我有一个字符串2013-11-08T10:11:31+02:00，我想将它转换为DATE数据类型（在本地数据库时区中+04:00 (Europe/Moscow)）。
For string 2013-11-08T10:11:31+02:00my desired transformation should return DATEdata 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:00in string above is just example.
对于字符串，2013-11-08T10:11:31+02:00我想要的转换应该返回DATE带日期的数据类型2013-11-08 12:11:31（即本地时区转换时间为+04:00 (Europe/Moscow)）。字符串的时区可能不同，+02:00上面的字符串只是示例。
I tried to do this with TIMESTAMPdata type, but no success with time zone transformation.
我尝试使用TIMESTAMP数据类型执行此操作，但时区转换没有成功。
回答by Nick Krasnov
to_timestamp_tz()function with at time zoneclause can be used to convert your string literal to a value of timestamp with time zonedata type:
to_timestamp_tz()函数 withat time zone子句可用于将字符串文字转换为timestamp with time zone数据类型的值：
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 of datedata type:
然后，我们使用cast()函数来产生一个date数据类型的值：
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 zoneclause and to_timestamp_tz()function.
了解有关at time zone子句和to_timestamp_tz()函数的更多信息。
回答by Rob van Laarhoven
SELECT
CAST((FROM_TZ(CAST(timezonefield AS TIMESTAMP),'GMT') AT TIME ZONE 'CET') AS DATE)
FROM table;
Converts a timestamp in GMT to date in Central European time
将 GMT 中的时间戳转换为中欧时间中的日期
回答by Pancho
if you want your timestamp with timezone to convert to a date in sync with "sysdate" then use the following:
如果您希望带时区的时间戳转换为与“sysdate”同步的日期，请使用以下命令：
select CAST(to_timestamp_tz('2013-11-08T10:11:31-02:00',
'yyyy-mm-dd"T"hh24:mi:sstzh:tzm') at time zone to_char(systimestamp,
'tzh:tzm') AS DATE)
from dual
回答by Bhushan Shimpi
to cast time stamp to date :
将时间戳转换为日期：
cast(registrationmaster.Stamp5DateTime as date) >= '05-05-2018' AND cast(registrationmaster.Stamp5DateTime as date) <= '05-05-2018'
cast(registrationmaster.Stamp5DateTime as date) >= '05-05-2018' AND cast(registrationmaster.Stamp5DateTime as date) <= '05-05-2018'
