Earlier today a situation came up where a UNIX timestamp (a count of the number of seconds from January 1, 1970, midnight GMT) needed to be converted into an Oracle DATE format. The Oracle TO_DATE (covered in more detail in my article Oracle, SQL, Dates and Timestamps) does not support this type of conversion.
A Google search confirmed for me that their was no easy way to make the TO_DATE function do this, but I did find this article from the Oracle + PHP Cookbook on Oracle’s site which contained this simple function to convert UNIX timestamps to Oracle dates.
[SQL]CREATE OR REPLACE
FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
* Converts a UNIX timestamp into an Oracle DATE
unix_epoch DATE := TO_DATE(‘19700101000000′,’YYYYMMDDHH24MISS’);
max_ts PLS_INTEGER := 2145916799; — 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; — 1903-01-01 00:00:00
IF unixts > max_ts THEN
‘UNIX timestamp too large for 32 bit limit’
ELSIF unixts < min_ts THEN RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too small for 32 bit limit' ); ELSE oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND'); END IF; RETURN (oracle_date); END; / [/SQL] Once compiled, you can use the function to convert numerical UNIX timestamps into Oracle dates in the same manner you would use the TO_DATE function to convert text to dates. Here is an example:
SQL> select unixts_to_date(1094165422) from dual;