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


Comments are closed.


(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.

OakTableLogo-small OCMOCMracattack-icon


Jeremy Schneider

Get every new post delivered to your Inbox.

Join 931 other followers

%d bloggers like this: