将HEXTORAW值转换为TIMESTAMP

Question

我正在研究一些分析工具，我有一个特定的情况，我有一个值，使用HEXTORAW Oracle函数表示如下:

HEXTORAW('7876031b0d233900786450')

这个值表示的是TO_TIMSTAMP('2018-03-27 12:34:56.00789')。到目前为止，我已经找到了两种方法来尝试转换这个HEXTORAW值:

• 使用dbms_stats.convert_raw_to_date
在PL/SQL块中使用dbms_stats.convert_raw_value

在这两种情况下，返回值都没有时间戳的分形秒部分，我怀疑这可能是由于底层调用依赖于DATE数据类型而不是真正的TIMESTAMP数据类型。

有谁知道是否有另一个PL/SQL包函数，可以用来解码原始值回一个完整的时间戳，包括分形秒?

Answer 1

没有将十六进制字符串转换为TIMESTAMP的函数，但是可以很容易地计算出TIMESTAMP的值。

将前7个字节转换为DATE，然后将其转换为TIMESTAMP(9)(它将具有0的小数秒数)，然后您可以将最后4个字节转换为一个数字，它表示纳秒数，然后将其添加到时间戳:

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
)

输出:

tbody> <<tr>

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