ORACLE Casting DATE to TIMESTAMP WITH TIME ZONE WITH OFFSET

Question

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?

Answer 1

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

Answer 2

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!