inserting unixtime in database

[smitanaik]

hi
i have unixtime format 1075329297.572 how do i store it in database.that is with what datatype.i tried to store unixtime using date, timestamp but it does not work.
but with varchar2 it works. but ifi store with varchar2 i cant perform conversion functions.
i want to convert unix time into yyyy-dd-mm hh-mm-ss format.
help me out
thanks in advance

[amitpatel66]

Try this function that converts Unix_Time to Date:

CREATE OR REPLACE
    FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
        /**
         * Converts a UNIX timestamp into an Oracle DATE 
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
        min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
        oracle_date DATE;
 
        BEGIN
 
            IF unixts> max_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too large for 32 bit limit'
                );
            ELSIF unixts <min_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too small for 32 bit limit' );
            ELSE
                oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
            END IF;
 
            RETURN (oracle_date);
 
END;
/