Re: epoch time

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 15 Nov 2006 07:13:18 -0800
Message-ID: <1163603598.313764.162640@i42g2000cwa.googlegroups.com>

On Nov 15, 7:48 am, "Martin T." <bilbothebagginsb..._at_freenet.de> wrote:
> Lakshmi wrote:
> > Hi
>
> > Can date be stored and retrived as unix epoch time in database?
>
> > Thanks
> > Lakshmideclare
> x TIMESTAMP WITH TIME ZONE;
> begin
> x := TO_TIMESTAMP_TZ('1970-01-01 00:00:00 +00:00', 'YYYY-MM-DD
> HH24:MI:SS TZH:TZM');
> end;

It would be unadvisable to store the date as an epoch in the database. Instead just convert the Oracle date or timestamp data type column value to an epoch relative value on extraction.

UT1> l
  1 select to_number(sysdate - to_date('01-JAN-1970','DD-MON-YYYY'))   2 * (24 * 60 * 60) "C Time"
  3 from sys.dual
  4*
UT1> /

    C Time

---

1163585314

To store just reverse the logic and add the epoch value in seconds / days to the epoch start date.

Otherwise use a number data type column, but like I said you should really store the value in an Oracle native data type then you can convert it as desired to C-time, Java Time, Excel, etc....

HTH -- Mark D Powell -- Received on Wed Nov 15 2006 - 09:13:18 CST