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.

PLAIN TEXT
SQL:
  1. CREATE OR REPLACE
  2.     FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
  3.         /**
  4.          * Converts a UNIX timestamp into an Oracle DATE
  5.          */
  6.         unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
  7.         max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
  8.         min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
  9.         oracle_date DATE;
  10.        
  11.         BEGIN
  12.            
  13.             IF unixts> max_ts THEN
  14.                 RAISE_APPLICATION_ERROR(
  15.                     -20901,
  16.                     'UNIX timestamp too large for 32 bit limit'
  17.                 );
  18.             ELSIF unixts <min_ts THEN
  19.                 RAISE_APPLICATION_ERROR(
  20.                     -20901,
  21.                     'UNIX timestamp too small for 32 bit limit' );
  22.             ELSE
  23.                 oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
  24.             END IF;
  25.            
  26.             RETURN (oracle_date);
  27.        
  28. END;
  29. /

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;

UNIXTS_TO
---------
02-SEP-04

oracle, sql, dba, database administration, database development, unix, unix timestamp, timestamp