7

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Max_Salah
  • 2,407
  • 11
  • 39
  • 68
  • 2
    My first guess would be the format string needs something for the offset. This question might help http://stackoverflow.com/questions/588286/oracle-timestamp-with-timezone-named-zone-vs-offset – Keith John Hutchison Jul 13 '16 at 08:42
  • 2
    Found an older topic: http://stackoverflow.com/questions/22245250/oracle-casting-date-to-timestamp-with-time-zone-with-offset – Rene Jul 13 '16 at 08:49

1 Answers1

17

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
MT0
  • 143,790
  • 11
  • 59
  • 117
  • 1
    Thnak you. But the function should be static and can called from overall. It is not specific to be retrieved from EUROE – Max_Salah Jul 13 '16 at 08:52
  • 3
    @Max_Salah Use `AT LOCAL` rather than `AT TIME ZONE ...`. – MT0 Jul 13 '16 at 08:58
  • 1
    is it possible to conver to the local time even if the TIME zone is unknown? something like FROM_TZ( CAST( value AS TIMESTAMP ), Server time zone...? – Max_Salah Jul 13 '16 at 10:07
  • 2
    You could use `TO_CHAR( SYSTIMESTAMP, 'TZR' )` to get the system's time zone region and use that as the parameter for `FROM_TZ()`. – MT0 Jul 13 '16 at 10:20