Convert HEXTORAW value to TIMESTAMP

Question

I am working on some analysis tools and I have a specific situation where I have a value that is represented using the HEXTORAW Oracle function as follows:

HEXTORAW('7876031b0d233900786450')

This value represents what would otherwise be written as TO_TIMSTAMP('2018-03-27 12:34:56.00789'). So far I have found two ways to attempt to convert this HEXTORAW value:

Using dbms_stats.convert_raw_to_date
Using dbms_stats.convert_raw_value in a PL/SQL block

In both cases, the return value doesn't have the fractal seconds portion of the timestamp and I suspect this is likely due to the underlying calls relying on a DATE data type rather than a true TIMESTAMP data type.

Does anyone know if there is another PL/SQL package function that could be used to decode the raw value back to a complete timestamp that includes the fractal seconds?

user_1509264 · Answer

There is not a function that converts a hex-string to a TIMESTAMP but you can easily calculate the TIMESTAMP value.

Convert the first 7 bytes to a DATE and then cast it to a TIMESTAMP(9) (which will have fractional seconds of 0) and then you can convert the last 4 bytes to a number, which represents the number of nanoseconds and then add that to the timestamp:

SELECT CAST(
         DBMS_STATS.CONVERT_RAW_TO_DATE(HEXTORAW(SUBSTR(hex_value,1,14)))
         AS TIMESTAMP(9)
       )
       + INTERVAL '1' SECOND(9)
         * TO_NUMBER(HEXTORAW(SUBSTR(hex_value,15,8)), 'XXXXXXXX') / 1e9
         AS timestamp_val,
       hex_value
FROM   (
  SELECT '7876031b0d233900786450' AS hex_value FROM DUAL
)

Outputs:

TIMESTAMP_VAL HEX_VALUE

2018-03-27 12:34:56.007890000 7876031b0d233900786450

and

CREATE TABLE table_name (value TIMESTAMP(9));
INSERT INTO table_name (value) VALUES (TIMESTAMP '2018-03-27 12:34:56.007890000');

SELECT value, DUMP(value,16) FROM table_name

Outputs:

VALUE DUMP(VALUE,16)

2018-03-27 12:34:56.007890000 Typ=180 Len=11: 78,76,3,1b,d,23,39,0,78,64,50

Which has the same timestamp and same byte values.

If you want to wrap it into a function then:

CREATE FUNCTION hex_to_timestamp(
  hex_value IN VARCHAR2
) RETURN TIMESTAMP DETERMINISTIC
IS
BEGIN
  RETURN CAST(
           DBMS_STATS.CONVERT_RAW_TO_DATE(HEXTORAW(SUBSTR(hex_value,1,14)))
           AS TIMESTAMP
         )
         + INTERVAL '1' SECOND
           * TO_NUMBER(HEXTORAW(SUBSTR(hex_value,15,8)), 'XXXXXXXX') / 1e9;
END;
/

db<>fiddle here

Update

Perhaps the "simplest" method is to use something that will already convert a byte string to a timestamp. In this case, the Java class oracle.sql.TIMESTAMP has a constructor that takes a byte array (and there is a similar constructor for oracle.sql.TIMESTAMPTZ).

Therefore, if you have Java enabled in the database, you can use a small class to convert the hex-string to a byte array and then to wrap the constructors in functions:

CREATE AND COMPILE JAVA SOURCE NAMED HexToTimestampConverter AS
import oracle.sql.TIMESTAMP;
import oracle.sql.TIMESTAMPTZ;

public class HexToTimestampConverter {
  public static byte[] hexStringToByteArray(String s) {
    int len = s.length();
    byte[] data = new byte[len / 2];
    for (int i = 0; i < len; i += 2) {
      data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) << 4)
                           + Character.digit(s.charAt(i+1), 16));
    }
    return data;
  }

  public static TIMESTAMP hexToTimestamp(final String hex)
  {
    return new TIMESTAMP(hexStringToByteArray(hex));
  }

  public static TIMESTAMPTZ hexToTimestamptz(final String hex)
  {
    return new TIMESTAMPTZ(hexStringToByteArray(hex));
  }
}

Then you can create an SQL function to call the Java code:

CREATE FUNCTION hex_to_timestamp( in_value IN VARCHAR2 ) RETURN TIMESTAMP
AS LANGUAGE JAVA NAME 'HexToTimestampConverter.hexToTimestamp(java.lang.String) return oracle.sql.TIMESTAMP';
/

And for TIMESTAMP WITH TIME ZONE:

CREATE FUNCTION hex_to_timestamptz( in_value IN VARCHAR2 ) RETURN TIMESTAMP WITH TIME ZONE
AS LANGUAGE JAVA NAME 'HexToTimestampConverter.hexToTimestamptz(java.lang.String) return oracle.sql.TIMESTAMPTZ';
/

(Note: you probably want to add some error checking to check that the string is hexadecimal and of the correct length.)

db<>fiddle here

Convert HEXTORAW value to TIMESTAMP

1 Answers

Update