How to convert Unix timestamp to Datetime in PL/SQL

🎈 🎈 🎈


    SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(UnixTimestamp, 'SECOND') AS ConvertedDate
    FROM your_table;

Output:

--------------------------- | ConvertedDate | --------------------------- | 2020-06-04 09:00:31 |

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

Code Explanation

The TO_DATE function is used to define the Unix epoch start date ('1970-01-01'). The NUMTODSINTERVAL function then converts the Unix timestamp, eg 1591261231, into an interval of time in seconds.
This interval is added to the Unix epoch date to obtain the corresponding date and time. The resulting ConvertedDate is the human-readable date, eg 2020-06-04 09:00:31, that corresponds to the Unix timestamp.

Convert Date to Unix timestamp 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