Convert from milliseconds since epoch to Oracle timestamp

Today I needed to convert from numbers in a log file originally generated using System.currentTimeMillis() in Java to Oracle timestamps and I wanted to do it with PL/SQL, without involving Java in the database. I love Java, but inside Oracle? No thanks.

Surely this is simple and surely the Internet abounds with examples? No and no. It is easy to create a date, but date columns loose precision. I needed to preserve the milliseconds. There are many examples for getting the number of milliseconds since the epoch from an Oracle timestamp, but not for creating a timestamp from a millisecond value.

After some thinking I ended up with the following code:

create or replace function millis_to_timestamp(p_millis in number)
  return timestamp is
  v_datePart date;
  v_millisPart number;
begin
  v_datePart := to_date('19700101', 'YYYYMMDD') + (p_millis / 86400000);
  v_millisPart := mod(p_millis, 1000);
  return to_timestamp(to_char(v_datePart, 'YYYYMMDDHH24MISS') ||
         substr(to_char(v_millisPart + 1000), 2, 3), 'YYYYMMDDHH24MISSFF');
end;

Not that pretty, but it works! Share and enjoy…