ORACLE Casting DATE to TIMESTAMP WITH TIME ZONE WITH OFFSET

14
votes

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.

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?

3
sqloracletimestamp-with-timezone
When you use CAST({DATE} AS TIMESTAMP WITH TIME ZONE) then Oracle takes your current SESSIONTIMEZONE - which is obviously set to Europe/Paris – Wernfried Domscheit
An important question would be, if you want the result to be text or actual TIMESTAMP WITH TIME ZONE, and the client consuming the result will understand the type (and hence should be able to understand, that EUROPE/PARIS has +01:00 offset for this particular time? – Hilarion

3 Answers

25
votes

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

SQL> SELECT from_tz(CAST (SYSDATE AS TIMESTAMP), '+01:00') tz FROM dual;
TZ
-------------------------------------------------
07/03/14 09:47:06,000000 +01:00
8
votes

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.

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
votes

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