[SOLVED] Oracle - Convert UTC Date to Local Date Time US Format

Oracle - Convert UTC Date to Local Date Time US Format

I have datetime in the UTC timezone and would like to convert it to the US datetime format MM/DD/YYYY and adjust to the local time zone.

Got it converted in a few steps via a script ...

Do you think is possible to do it using a SELECT statement? Not sure if we can know the timezone configured on the server. Would be glad to get some suggestions. Thanks.

Solution

Here is a step-by-step of the process

I will start with a timestamp in UTC

SQL> select timestamp '2021-07-01 09:00:00 +00:00' ts from dual;
    
    TS
    ---------------------------------------------------------------------
    01-JUL-21 09.00.00.000000000 AM +00:00

Assuming this is stored in some sort of table, the first thing we can do is convert it to the desired time zone. In my case, I live in Perth, Australia so I do:

SQL> with my_table as (
  2  select timestamp '2021-07-01 09:00:00 +00:00' ts from dual
  3  )
  4  select ts at time zone 'Australia/Perth' as perth_time
  5  from my_table;
    
    PERTH_TIME
    ---------------------------------------------------------------
    01-JUL-21 05.00.00.000000000 PM AUSTRALIA/PERTH

9am UTC is 5pm in Perth (we're 8 hours in front). Now I want that output in a format that I want, so I can TO_CHAR that in the normal way

SQL> with my_table as (
  2  select timestamp '2021-07-01 09:00:00 +00:00' ts from dual
  3  )
  4  select to_char(ts at time zone 'Australia/Perth','MM/DD/YYYY HH24:MI:SS') as perth_time
  5  from my_table;

    PERTH_TIME
    -------------------
    07/01/2021 17:00:00

and we're done