Converting Oracle DATE types and PHP/Unix Timestamps

If you need to convert between Oracle date and PHP/Unix timestamp then you might need to implement two functions in Oracle.

The following two Oracle functions implement this for DATE types.

To convert a UNIX timestamp into an Oracle DATE type:

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
        oracle_date DATE;

        BEGIN

            IF unixts > max_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    '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;

The following PHP script shows how this might be used. Note that this script requires PHP 5.x+, as it uses the new OCI extension function names:

<?php
$conn = oci_connect('scott', 'tiger');

$sql = "
    SELECT
        *
    FROM EMP
    WHERE
        hiredate
    BETWEEN
        unixts_to_date(:startdate)
    AND
        unixts_to_date(:enddate)
    ORDER BY
        hiredate
    DESC
    ";

$stmt = oci_parse($conn, $sql);

// Bind a UNIX timestamps to :startdate and :enddate
oci_bind_by_name($stmt, ":startdate", mktime(0,0,0,1,1,1981));
oci_bind_by_name($stmt, ":enddate", mktime(0,0,0,1,1,1990));

oci_execute($stmt);

print "NAME  : HIREDATE\n";
while ( $row = oci_fetch_assoc($stmt) ) {
   print "{$row['ENAME']} : {$row['HIREDATE']}\n";
}
oci_free_statement($stmt);

oci_close($conn);
?>

In reverse, the following function returns a UNIX timestamp given an Oracle DATE type:

CREATE OR REPLACE
    FUNCTION date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
        /**
         * Converts an Oracle DATE to a UNIX timestamp
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
        min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
        unix_ts PLS_INTEGER;

        BEGIN

            IF oracle_date > max_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
            ELSIF oracle_date < min_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
            ELSE
                unix_ts := (oracle_date - unix_epoch) / (1/86400);
            END IF;

            RETURN (unix_ts);

        END;

The following query shows how it might be used:SELECT
    ename,
    TO_CHAR(hiredate, 'YYYY') AS hired_year,
    TO_CHAR(hiredate, 'YYYYMM') AS hired_month,
    TO_CHAR(hiredate, 'ddth Mon, YYYY') AS hired_pretty
    date_to_unixts(hiredate) AS hired_unixts
FROM
    emp
ORDER BY
    hiredate

It’s now easy to convert the timestamp into a formatted date, using the date() function as you loop through the result set.