17

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?

Farlop
  • 690
  • 1
  • 6
  • 20
  • 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 Jun 22 '18 at 09:17
  • 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 Jan 31 '20 at 10:48

3 Answers3

28

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
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • Great solution! This allow us to set the time zone we want to, depending of the source of the data. Thanks a lot! – Farlop Mar 07 '14 at 10:45
8

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!

RandomUser
  • 1,094
  • 1
  • 12
  • 24
  • 4
    Actually it's a better solution! With this solution, there's no need to hardcode the timezone in the SQL script. – Rick Jan 28 '16 at 11:26
  • 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. – Wernfried Domscheit Jun 22 '18 at 09:15
  • 3
    @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 https://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 – Hamish Carpenter Jul 12 '18 at 01:57
  • 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. – Hilarion Jan 31 '20 at 10:51
-2

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

Dan
  • 67
  • 1
  • 5