time conversion

[smitanaik]

1075329297.572
This is unix time where 1075329297 is seconds 572 is milliseconds.
first il store this time in oracle database.when i am retrieving it i want this date into yyyy-dd-mm format. Is it possible to do it in oracle.(using convert function)
or is there some other way?
reply

[amitpatel66]

1075329297.572
This is unix time where 1075329297 is seconds 572 is milliseconds.
first il store this time in oracle database.when i am retrieving it i want this date into yyyy-dd-mm format. Is it possible to do it in oracle.(using convert function)
or is there some other way?
reply

Try this code:

[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;
/

SQL> SELECT unixts_to_date( 1075329297) from dual

[/code]