SQL ORACLE 将日期转换为带偏移时区的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22245250/
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 Casting DATE to TIMESTAMP WITH TIME ZONE WITH OFFSET
提问by Farlop
I need to cast a DATE value in a query to a TIMESTAMP WITH TIME ZONE, but currently I'm getting the TimeZone Region ('Europe / Paris') which is not valid to be used by EF.
我需要将查询中的 DATE 值转换为 TIMESTAMP WITH TIME ZONE，但目前我正在获取 EF 无效的时区区域（“欧洲/巴黎”）。
For example, when doing this:
例如，在执行此操作时：
select CAST(FECHA AS TIMESTAMP WITH TIME ZONE) from test;
I currently get this output:
我目前得到这个输出：
07/03/14 09:22:00,000000000 EUROPE/PARIS
But I need it to be like:
但我需要它像：
07/03/14 09:22:00,000000000 +01:00
Any idea how to accomplish this?
知道如何做到这一点吗？
回答by Vincent Malgrat
You can cast the DATEto a TIMESTAMP, then use FROM_TZto convert this timestamp to a timestamp with time zone:
您可以将 转换DATE为 a TIMESTAMP，然后使用FROM_TZ将这个时间戳转换为带时区的时间戳：
SQL> SELECT from_tz(CAST (SYSDATE AS TIMESTAMP), '+01:00') tz FROM dual;
TZ
-------------------------------------------------
07/03/14 09:47:06,000000 +01:00
回答by RandomUser
With @Vincent Malgrat solution you need to get the TIMEZONE_HOUR and then, format it to use in your query. I don't know if there is any chance to make it automatically.
使用@Vincent Malgrat 解决方案，您需要获取 TIMEZONE_HOUR，然后将其格式化以在查询中使用。不知道有没有机会自动制作。
I can suggest you to nest some functions. It is not the cleanest solution but it works for me
我可以建议你嵌套一些函数。这不是最干净的解决方案，但对我有用
SELECT TO_TIMESTAMP_TZ(TO_CHAR(CAST(FECHAHORA AS TIMESTAMP WITH TIME ZONE), 'DD-MM-YY HH24:MI:SS TZH:TZM'), 'DD-MM-YY HH24:MI:SS TZH:TZM' )FROM TEST;
And the result will be something like
结果将类似于
03/03/14 09:58:02,000000000 +01:00
Regards!
问候！
回答by Dan
Use ALTER SESSION SET TIME_ZONE = '+01:00'; before your SELECT
使用 ALTER SESSION SET TIME_ZONE = '+01:00'; 在您选择之前
