Oracle how to convert time in UTC to the local time (offset information is missing)

Question

I have a table which contains a date column. I think that the date in that column is saved in UTC.

I would like when the date is retrieved that it is printed in local time. That means when I call the date from Germany, the result should be something like this:

2015-04-29 11:24:06 +0200UTC EUROPE/BERLIN

I tried the following sql:

SELECT TO_CHAR(CAST(dateColum as  TIMESTAMP WITH LOCAL TIME ZONE), 'YYYY-MM-DD HH24:MI:SS TZR') from myTable;

the result looks like this:

2015-04-29 11:24:06 EUROPE/BERLIN

+/- offset is missing.

Any idea?

Answer 1

Oracle Setup:

CREATE TABLE table_name ( value ) AS
SELECT DATE '2016-07-13' FROM DUAL;

Query:

SELECT TO_CHAR(
         FROM_TZ( CAST( value AS TIMESTAMP ), 'UTC' )
           AT TIME ZONE 'EUROPE/BERLIN',
         'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
       ) AS berlin_time
FROM   table_name;

Output:

BERLIN_TIME
----------------------------------------
2016-07-13 02:00:00 +02:00 EUROPE/BERLIN

Query 2:

SELECT TO_CHAR(
         FROM_TZ( CAST( value AS TIMESTAMP ), 'UTC' ) AT LOCAL,
         'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
       ) AS local_time
FROM   table_name;

Output:

LOCAL_TIME
----------------------------------------
2016-07-13 02:00:00 +02:00 EUROPE/BERLIN