ORACLE使用OFFSET将DATE转换为具有时区的TIMESTAMP-ORACLECastingDATEtoTIMESTAMPWITHTIMEZONEWITHOFFSET

14  

You can cast the DATE to a TIMESTAMP, then use FROM_TZ to convert this timestamp to a timestamp with time zone:

您可以将DATE强制转换为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

3  

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!

-2  

Use ALTER SESSION SET TIME_ZOnE= '+01:00'; before your SELECT

使用ALTER SESSION SET TIME_ZOnE='+ 01:00';在你的SELECT之前