In Oracle Database, we can use the following technique to return a date from a Unix timestamp value.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).

Example

Here’s an example of converting a Unix timestamp to a DATE value:

SELECT 
    TO_DATE( '1970-01-01', 'YYYY-MM-DD' ) + NUMTODSINTERVAL( 1650321073, 'SECOND' )
FROM DUAL;

Result:

18-APR-22

Here, we use the TO_DATE() function to construct a date of 1970-01-01. We then add our Unix timestamp to that date to get our result. In this case, we use NUMTODSINTERVAL() to convert the Unix timestamp into an interval value. The result is a DATE value.

Get the Date and Time

We can use the TO_TIMESTAMP() function to output a timestamp value:

SELECT 
    TO_TIMESTAMP( '1970-01-01', 'YYYY-MM-DD' ) + NUMTODSINTERVAL( 1650321073, 'SECOND' )
FROM DUAL;

Result:

18-APR-22 10.31.13.000000 PM

Another way to do it is like this:

SELECT TO_CHAR( 
    TO_DATE( '1970-01-01', 'YYYY-MM-DD' ) + numtodsinterval( 1650321073, 'SECOND' ), 
    'YYYY-MM-DD HH24:MI:SS'
    )
FROM DUAL;

Result:

2022-04-18 22:31:13

Here, we use the TO_CHAR() function to output the result returned by TO_DATE() in our preferred format. This function returns its result as a VARCHAR2 value.