Oracle Timestamp Converter
Oracle Database has a rich set of date and time data types and functions, but it doesn't have a native Unix timestamp function like MySQL's UNIX_TIMESTAMP() or PostgreSQL's EXTRACT(EPOCH FROM ...). Converting between Oracle dates and Unix timestamps requires arithmetic based on the epoch reference date (January 1, 1970).
Oracle's primary datetime types are DATE (stores date and time with second precision), TIMESTAMP (stores date and time with fractional second precision), TIMESTAMP WITH TIME ZONE (stores the offset or timezone name), and TIMESTAMP WITH LOCAL TIME ZONE (converts to the session timezone on retrieval). For Unix timestamp work, TIMESTAMP WITH TIME ZONE provides the most complete information.
Oracle timestamp functions
Current date and time (local):
SELECT SYSDATE FROM DUAL;
Current timestamp with fractional seconds:
SELECT SYSTIMESTAMP FROM DUAL;
Current UTC timestamp:
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL;
Oracle DATE to Unix timestamp (seconds):
SELECT (SYSDATE - DATE '1970-01-01') * 86400 FROM DUAL;
Unix timestamp to Oracle DATE:
SELECT DATE '1970-01-01' + (1700000000 / 86400) FROM DUAL;
Parse a string to TIMESTAMP:
SELECT TO_TIMESTAMP('2026-04-17 10:35:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
Format a date as string:
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;