Convert Raw/Hex to Timestamp

Just filing this away on my own blog because it seems that I always have hard finding it via google.  Here’s some code to convert raw/hex values into timestamps.  (I’ve come across this need in two situations: [1] bind variables recorded in trace files or sql monitor and [2] hi/low values in column statistics.)

As a query, without creating any objects in the database:

-- this SQL will honor your nls_timestamp_format settings
undef raw_timestamp
select to_timestamp(
        to_char( to_number( substr( p_str, 1, 2 ), 'xx' ) - 100, 'fm00' ) ||
        to_char( to_number( substr( p_str, 3, 2 ), 'xx' ) - 100, 'fm00' ) ||
        to_char( to_number( substr( p_str, 5, 2 ), 'xx' ), 'fm00' ) ||
        to_char( to_number( substr( p_str, 7, 2 ), 'xx' ), 'fm00' ) ||
        to_char( to_number( substr( p_str,9, 2 ), 'xx' )-1, 'fm00' ) ||
        to_char( to_number( substr( p_str,11, 2 ), 'xx' )-1, 'fm00' ) ||
        to_char( to_number( substr( p_str,13, 2 ), 'xx' )-1, 'fm00' ), 'yyyymmddhh24miss' )
from (select '&raw_timestamp' p_str from dual);


  • As a function, from Tom Kyte in 2007 – for some reason Tom’s query had different offsets for the time components of the hex string (10,12,14 instead of 9,11,13) and his query does not work correctly for me on
  • Directly query table statistics, from Martin Widlake in 2010

About Jeremy

Doing stuff with Oracle Database, Performance, Clusters, Linux. about.me/jeremy_schneider


No comments yet.

Leave a New Comment

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


(a) This is my personal blog.

The views expressed on this website are mine alone and do not necessarily reflect the views of my employer.



Jeremy Schneider

Get every new post delivered to your Inbox.

Join 931 other followers

%d bloggers like this: