Re: How to create date directly from UNIX time

From: John K. Hinsdale <hin_at_alma.com>
Date: 10 Jan 2007 07:35:33 -0800
Message-ID: <1168443326.742146.222540@i39g2000hsf.googlegroups.com>

> On Jan 10, 9:28 am, krivenok.dmi..._at_gmail.com wrote:
>
> > How can I create object of DATE type directly from UNIX time?
> > to_date doesn't provide this facility.

sybrandb wrote:
> Just add to_date('01-01-1970', 'dd-mm-yyyy') to your Unix time.

Krivenok, Sybrand,

adding the integer Unix time to the result of TO_DATE as above will interpret (and add) the integer timestamp in days, not seconds (which are the granularity of Unix's timestamp).

So I believe you need to scale the Unix time by the number of seconds in a day, e.g.:

   to_date('01-01-1970', 'dd-mm-yyyy') + unix_time/86400

Using an example timestamp from my time in New Jersey USA right now, 1,168,442,762 seconds since midnight, Jan 1, 1970 GMT ...

SQL> select to_date('01-01-1970', 'dd-mm-yyyy') + 1168442762/86400 from dual;

TO_DATE('01-01-1970

---

2007-01-10 15:26:02

Just adding the raw Unix timestamp gave a nonsensical result. Note the above is GMT.

Hope that helps,

--JH Received on Wed Jan 10 2007 - 09:35:33 CST