Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR';
ALTER SESSION SET TIME_ZONE = 'Europe/Paris';
CREATE TABLE test_table (dt) AS
SELECT DATE '1970-01-02' FROM DUAL;
1 rows affected

SELECT SESSIONTIMEZONE,
       DT,
       ROUND((CAST(CAST(dt AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400, 6) AS EPOCH_DT
FROM   test_table


SESSIONTIMEZONE
DT
EPOCH_DT


Europe/Paris
1970-01-02 00:00:00
82800


CREATE TABLE test_table2 (ts TIMESTAMP WITH TIME ZONE);
INSERT INTO test_table2 (ts) VALUES (TIMESTAMP '1970-01-02 00:00:00 Europe/Paris');
1 rows affected

INSERT INTO test_table2 (ts) VALUES (TIMESTAMP '1970-01-02 00:00:00 America/New_York');
1 rows affected

SELECT ts,
       ROUND((CAST(CAST(ts AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400, 6) AS EPOCH_DT
FROM   test_table2


TS
EPOCH_DT


1970-01-02 00:00:00.000000 EUROPE/PARIS
82800

1970-01-02 00:00:00.000000 AMERICA/NEW_YORK
104400

TS	EPOCH_DT
1970-01-02 00:00:00.000000 EUROPE/PARIS	82800
1970-01-02 00:00:00.000000 AMERICA/NEW_YORK	104400