Re: Calculating Oracle date from Unix time

A copy of this was sent to "Luc Bomans" <luc_at_staf.planetinternet.be> (if that email address didn't require changing) On Thu, 9 Sep 1999 22:59:59 +0200, you wrote:

>In Unix time is often calculated or stored as an integer which
>stores the number of seconds since the first of January 1970.
>
>For example:
>935653961 corresponds to Thu Aug 26 09:52:41 1999
>
>In Perl:
>
>perl -e '$ltime=localtime(935653961); print "$ltime\n";'
>Thu Aug 26 09:52:41 1999
>
>
>The software package we use, also stores dates in this way
>in the Oracle database (so all dates are integers).
>
>Is there a function in Oracle to derive the Oracle date from
>such a value ?
>
>
>Thanks for your input
>Luc Bomans
>Planet Internet Belgium
>
>

If you know your timezone, yes

...
time_t theTime;
varchar otime[50];

    time(&theTime);

    printf( "%s", ctime( &theTime ) );

    EXEC SQL SELECT to_char(

        new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * :theTime,
        'GMT', 'EDT' ), 'dd-mon-yyyy hh24:mi:ss' )
        into :otime from dual;

Wed May 13 21:35:06 1998
13-may-1998 21:35:06

this will do it in the timezone EDT....

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Sep 13 1999 - 09:07:13 CDT