oracle-databasetimezone-offset

How to add the timezone offset to a timestamp?


How can I add the time zone offset in the third query?

SELECT SESSIONTIMEZONE FROM DUAL;
# +01:00

SELECT TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL;
# +01:00

SELECT TO_CHAR(TO_TIMESTAMP('19700101000000','YYYYMMDDHH24MISS')+NUMTODSINTERVAL(86400,'SECOND'),'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
# 1970-01-02 00:00:00

The desired result would be

# 1970-01-02 01:00:00

Solution

  • Use FROM_TZ to convert your TIMESTAMP to a TIMESTAMP WITH TIME ZONE on the UTC time zone and then use AT TIME ZONE SESSIONTIMEZONE to convert it to your local time and then convert it back to a string:

    ALTER SESSION SET TIME_ZONE='Europe/Paris';
    

    Then:

    SELECT TO_CHAR(
              FROM_TZ(
                TIMESTAMP '1970-01-01 00:00:00' + INTERVAL '86400' SECOND,
                'UTC'
              ) AT TIME ZONE SESSIONTIMEZONE,
              'YYYY-MM-DD HH24:MI:SS'
           ) AS ts
    FROM   DUAL
    

    or, more simply:

    SELECT TO_CHAR(
              (TIMESTAMP '1970-01-01 00:00:00 UTC' + INTERVAL '1' DAY)
              AT TIME ZONE SESSIONTIMEZONE,
              'YYYY-MM-DD HH24:MI:SS'
           ) AS ts
    FROM   DUAL
    
    TS
    1970-01-02 01:00:00

    fiddle