Re: Date / Time Stamp

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 28 Feb 2011 01:51:47 -0800 (PST)
Message-ID: <42304aab-7417-4589-9329-7e972d40f592_at_u6g2000vbh.googlegroups.com>

On 21 Feb., 17:46, Mladen Gogala <n..._at_email.here.invalid> wrote:
> 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;

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 Received on Mon Feb 28 2011 - 03:51:47 CST