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:

[code=oracle]
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('197001 01000000','YYYY MMDDHH24MISS');
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_APPLICATI ON_ERROR(
-20901,
'UNIX timestamp too large for 32 bit limit'
);
ELSIF unixts <min_ts THEN
RAISE_APPLICATI ON_ERROR(
-20901,
'UNIX timestamp too small for 32 bit limit' );
ELSE
oracle_date := unix_epoch + NUMTODSINTERVAL (unixts, 'SECOND');
END IF;

RETURN (oracle_date);

END;
/
[/code]