Home > Database, Oracle > Convert from milliseconds since epoch to Oracle timestamp

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…

Advertisements
Categories: Database, Oracle
  1. Braini
    2014-04-11 at 11:46

    Avoid wrong rounding when v_millisPart >= 500:

    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’) + (trunc(p_millis / 1000) / 86400);
    v_millisPart := mod(p_millis, 1000);
    return to_timestamp(to_char(v_datePart, ‘YYYYMMDDHH24MISS’) ||
    substr(to_char(v_millisPart + 1000), 2, 3), ‘YYYYMMDDHH24MISSFF’);
    end;

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: