Max_Salah Max_Salah - 4 months ago 59
SQL Question

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

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

I would like when the date is etrievee 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?

MT0 MT0
Answer

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
Comments