Re: Date / Time Stamp

From: Mladen Gogala <no_at_email.here.invalid>
Date: Mon, 21 Feb 2011 16:46:58 +0000 (UTC)
Message-ID: <pan.2011.02.21.16.46.58_at_email.here.invalid>

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

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

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

Received on Mon Feb 21 2011 - 10:46:58 CST