Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to create date directly from UNIX time
> 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
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
![]() |
![]() |