Hi,
I am trying to create a function that converts timestamps that are stored as a big number in a table, which is measured in 100s of nanoseconds into a more readable format (yyyy-mm-dd HH:mm:ss). The timestamps are collected from over 20 global locations. I would like to return date in each locations timezone.
here is my function:
create or replace function utcnumbertoutctime(
inputts in number) return timestamp deterministic
is
utc_time timestamp;
unix_epoch timestamp := to_timestamp('1970-01-01', 'yyyy-mm-dd');
millis_to_100ns number := 10000;
millis_per_day number := 86400000;
days_part int;
seconds_part int;/* seconds on the last day*/
inputts_h number;
begin
inputts_h := inputts;
if(inputts_h is null or inputts_h < getgregorianconstant()) then
/* default minimum */
utc_time := unix_epoch;
else
/* convert to millis since unix timestamp epoch 1970-1-1 */
inputts_h :=(inputts_h/millis_to_100ns)
days_part := inputts_h/millis_per_day;
/* number of days*/
seconds_part := mod(inputts_h, millis_per_day)/1000;
/* convert to utc timestamp */
utc_time := (days_part+unix_epoch);
utc_time := (seconds_part+utc_time);
end if;
return (utc_time);
end;
select UTCNUMBERTOUTCTIME(136649680990010000) from dual;
returns 12-JUL-86 12.00.00.000000000 AM
instead of 24-OCT-15 12.28.19.000000000 AM
Any help in the right direction is appreciated.
Thanks,
-Abe