How to add the timezone offset to a timestamp?

Question

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

Answer 1

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