>
Technical

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);

References:

  • 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 11.2.0.3
  • Directly query table statistics, from Martin Widlake in 2010
About these ads

About Jeremy

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

Discussion

Comments are closed.

Disclaimer

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

racattack-iconOakTableLogo-small

about.me

Jeremy Schneider
Follow

Get every new post delivered to your Inbox.

Join 926 other followers

%d bloggers like this: