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.
