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
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 |