How to convert Datetime to Unix timestamp in PL/SQL

🎈 🎈 🎈


    SELECT id,
    date_column,
    (date_column - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400 AS unix_timestamp
    FROM your_table;

Output:

----------------------------------------------- | id | date_column | unix_timestamp | ----------------------------------------------- | 01 | 2002-09-18 07:29:40 | 1032334180 |

Example only. There may be multiple ways to perform this operation.

Code Explanation

This PL/SQL query selects the 'id' and 'date_column' from 'your_table' and calculates the Unix timestamp by subtracting the Unix epoch date ('1970-01-01') from the 'date_column'.
This difference, initially in days, is then multiplied by 86400 (the number of seconds in a day) to convert it into seconds, which is the Unix timestamp.

Convert Unix timestamp to Date in PL/SQL

Other useful PL/SQL date functions

Function	Description	Example
`SYSDATE`	Returns the current date and time from the Oracle server in the format 'DD-MON-YYYY HH:MM:SS'.	`SYSDATE` returns 28-AUG-2024 12:34:56
`SYSTIMESTAMP`	Returns the current timestamp from the Oracle server, including date, time, and fractional seconds.	`SYSTIMESTAMP` returns 28-AUG-2024 12:34:56.789123 PM +00:00
`TO_DATE()`	Converts a string to a date value, according to the specified date format.	`TO_DATE('2024-08-28', 'YYYY-MM-DD')` returns 28-AUG-2024
`TO_CHAR()`	Converts a date or timestamp to a string, according to the specified format.	`TO_CHAR(SYSDATE, 'YYYY-MM-DD')` returns 2024-08-28
`ADD_MONTHS()`	Adds a specified number of months to a date.	`ADD_MONTHS(SYSDATE, 3)` returns 28-NOV-2024
`MONTHS_BETWEEN()`	Returns the number of months between two dates.	`MONTHS_BETWEEN('28-NOV-2024', SYSDATE)` returns 3
`TRUNC()`	Truncates a date to the specified unit (e.g., year, month, day).	`TRUNC(SYSDATE, 'MONTH')` returns 01-AUG-2024
`LAST_DAY()`	Returns the last day of the month for a given date.	`LAST_DAY(SYSDATE)` returns 31-AUG-2024
`NEXT_DAY()`	Returns the date of the next specified weekday after a given date.	`NEXT_DAY(SYSDATE, 'FRIDAY')` returns 30-AUG-2024
`EXTRACT()`	Extracts and returns a specific part (e.g., year, month, day) from a date or timestamp.	`EXTRACT(YEAR FROM SYSDATE)` returns 2024

Current Unix Timestamp SECONDS SINCE JAN 01 1970

Current UTC Time

2025-08-20