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?

Glottic

When you use CAST({DATE} AS TIMESTAMP WITH TIME ZONE) then Oracle takes your current SESSIONTIMEZONE - which is obviously set to Europe/Paris – Del

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? – Giffy

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

Prejudge

Great solution! This allow us to set the time zone we want to, depending of the source of the data. Thanks a lot! – Glottic

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!

Focalize

Actually it's a better solution! With this solution, there's no need to hardcode the timezone in the SQL script. – Vindicable

It is pointless to convert a DATE to a string with TO_CHAR and then convert back again to a timestamp value. Also the result depends on current user session SESSIONTIMEZONE. Try ALTER SESSION SET TIME_ZONE = '+12:00'; then result will be wrong. – Del

@WernfriedDomscheit, the double conversion is necessary to convert from a time zone region to a time zone offset. There is more information about the difference here stackoverflow.com/tags/timezone/info. With your example the output is the same as the input is an offset and the output is the same offset. What is implicit about this answer is that it uses the sessions's timezone – Gadolinium

Yes, the difference between a time zone and a time zone offset is important. This does not mean, that you need to go through two type conversions to achieve the result. The important thing is, that a timestamp with time zone, which uses actual time zone (not just an offset) is actually more precise, that a timestamp with time zone, where there's just offset. You can get the offset from both, you can't get the full time zone (region) when just having the offset. – Giffy

Answer 3

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

Dermato

Valid answer but some explanation would be useful. – Del