Hi,

Is there a good way to convert a date to the Unixtimestamp? We need
to do this. I used the function below, but our developers are tells
us it is not correct:

CREATE OR REPLACE FUNCTION EVENTS.oracle_to_unix_ts (
p_date DATE)
RETURN NUMBER IS

v_timestamp NUMBER;

BEGIN
SELECT (CAST(sysdate AS date) - p_date) * 86400 seconds
INTO v_timestamp
FROM DUAL;
RETURN v_timestamp;

END oracle_to_unix_ts;

On Mon, 21 Feb 2011 08:31:20 -0800, The Magnet wrote:

The developers tells us correctly. Me thinks it should be something like
this:
CREATE OR REPLACE FUNCTION EVENTS.oracle_to_unix_ts (
p_date DATE)
RETURN NUMBER IS
v_epoch DATE := to_date('01/01/1970','MM/DD/YYYY');
v_timestamp NUMBER;

BEGIN
SELECT (p_date-epoch) * 86400
INTO v_timestamp
FROM DUAL;
RETURN v_timestamp;

END oracle_to_unix_ts;



--
http://mgogala.byethost5.com

On Mon, 21 Feb 2011 08:31:20 -0800, The Magnet wrote:

Also, here is an official page from Oracle which shows you the algorithm:
http://blogs.sun.com/mock/entry/conv..._dates_to_unix



--
http://mgogala.byethost5.com

On 21 Feb., 17:46, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
Mladen, any reason why there is a SELECT in there? IMHO this is
sufficient

CREATE OR REPLACE FUNCTION oracle_to_unix_ts (
p_date DATE)
RETURN NUMBER IS
c_epoch CONSTANT DATE := TO_DATE('01/01/1970', 'MM/DD/YYYY');

BEGIN
RETURN (p_date - c_epoch) * 86400;
END oracle_to_unix_ts;

Not sure how Oracle deals with constants inside functions but it may
be more efficient to place the constant in a surrounding package.
This would make sense anyway because it would be needed for the
reverse conversion as well.

Cheers

robert