Oracle 18c

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

CREATE TABLE your_table (your_date) AS
SELECT SYSDATE FROM DUAL;
1 rows affected

SELECT TO_NUMBER(TO_CHAR(your_date, 'YYYYMMDDHH24MISS')) AS date_number
FROM   your_table;


DATE_NUMBER


20220426143648


SELECT ROUND((your_date - DATE '1970-01-01') * 86400) AS seconds_since_1970
FROM   your_table;


SECONDS_SINCE_1970


1650983808


ALTER TABLE your_table
  ADD date_number INTEGER
        GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(your_date, 'YYYYMMDDHH24MISS')));
ALTER TABLE your_table
  ADD seconds_since_1970 INTEGER
        GENERATED ALWAYS AS (ROUND((your_date - DATE '1970-01-01') * 86400));
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
SELECT * FROM your_table;


YOUR_DATE
DATE_NUMBER
SECONDS_SINCE_1970


2022-04-26T14:36:48
20220426143648
1650983808