Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Timezone conversion within select

3410314Mar 28 2018 — edited Mar 29 2018

Hi guys,

I am trying to add a timezone do a date field and then convert it to another time zone (in one select statement).

Why am I doing this:

I have a database, where all dates are UTC+0 (in summer as well as winter), but

select dbtimezone, sessiontimezone from dual;

results in:

+02:00 Europe/Berlin

which i cannot change. So in summer I Need to add +2h to the dates, and in winter +1h, to fit my actual location.

I tried adding the UTC timezone in the following variants:

FROM_TZ(CAST(MyDate AS TIMESTAMP), 'UTC' )   AS UTC_TXT

TO_TIMESTAMP_TZ(TO_CHAR(MyDate || ' +00:00'),'DD.MM.YYYY HH24:MI:SS TZH:TZM') AS UTC_NUM

and then cast it to the actual time zone, which does not work. Any ideas on how I can make it work or different approaches?

My approach:

TO_DATE(TO_CHAR((CAST(  FROM_TZ(CAST(MyDate AS TIMESTAMP), 'UTC' )   AS TIMESTAMP) AT TIME ZONE DBTIMEZONE),'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') AS Corrected_Date

Thanks in advance

This post has been answered by Paulzip on Mar 28 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 26 2018
Added on Mar 28 2018
4 comments
6,358 views