Re: Date conversions in PL/SQL

A copy of this was sent to "laurent dufosse" <laurent.dufosse_at_free.fr> (if that email address didn't require changing) On Wed, 15 Sep 1999 23:51:20 GMT, you wrote:

>I have a big problem to convert date in julian unix format (number of
>seconds from 1/1/1970)
>in a pl/sql package.
>Oracle don't understand julian at the same way in Unix.
>
>SO, is there every body have integrate an L3G-conversion in an Oracle 8i
>database (On HP-UX) ?
>
>thanks a lot for responses.
>
>laurent Dufosse
>

The Unix time format you are talking about above is *not* a julian date at all.

The JD counts days within one Julian Period of exactly 7980 Julian years of 365.25 days. Start of the JD count is 12 noon 1 JAN -4712, Julian proleptic calendar.

that aside, if you would like to convert the Unix 'seconds since 1970' number into a meaningful date, it is easy to do if you know your timezone:

...
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....

All you need to do is ADD the unix time to the date 01-01-1970 and adjust for GMT. Instead of using new_time to do the timezone conversion -- you can just add "N/24" where N is your offset from GMT

--
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 Thu Sep 16 1999 - 06:59:47 CDT