Re: Oracle 7.3 unix timestamp conversion

Stefan den Engelsman wrote
>I'm looking for a function within oracle which can convert a unix
timestamp
>( number of seconds in GMT since 1970.01.01 00:00:00) into an Oracle
date
>or time.

Oracle allows you to perform date calculations, representing the date as a number showing the number of days as of some date. Using this number represenation, which also has a fractional part, one can add another number to get a new date. To add a week: myDate + 7. Or, to add 5 minutes: myDate + 5 / ( 24*60 ). And to add 10 seconds: myDate + 10 / ( 24*60*60 ). Finally: you can use the new_time function to convert GMT into any timezone.

So, something like the following will do:

    create or replace function unix2ora

        ( pUnixDate in number
        , pTimeZone in varchar2 default 'GMT'
        ) return date

        return new_time( ( to_date( '01-JAN-1970', 'DD-MON-YYYY' )
            + ( pUnixDate / ( 24*60*60 ) ), 'GMT', pTimeZone );

By the way: you stated "into an Oracle date or time". Note that the Oracle date datatype always stores both date and time, although the time part may be set to 0:00:00.

Arjan. Received on Sat Dec 12 1998 - 18:21:52 CET