TIMESAMPE_HEX_TO_CHAR函數 解決時間16進位可視化問題，char轉16進位

TIMESAMPE_HEX_TO_CHAR函數 解決時間16進位可視化問題，char轉16進位

在11G 提供了一個超過4秒的SQL視圖 裡麵包含了很多必要的資訊 同時可以使用

--查看超過4秒語句執行計畫
SELECT dbms_sqltune.report_sql_monitor(sql_id => '&sql_id',report_level => 'ALL',type=>'TEXT') comm FROM dual;

獲得個格式美化的東西

 

可是其中

Binds
========================================================================================================================
| Name | Position |   Type    |                                         Value                                          |
========================================================================================================================
| :1   |        1 | TIMESTAMP | 78720C0D010101                                                                         |
| :2   |        2 | TIMESTAMP | 78720C0D183C3C                                                                         |
| :3   |        3 | NUMBER    | 3                                                                                      |
| :4   |        4 | NUMBER    | 10000978                                                                               |
| :5   |        5 | TIMESTAMP | 78720C0D010101                                                                         |
| :6   |        6 | TIMESTAMP | 78720C0D183C3C                                                                         |
| :7   |        7 | NUMBER    | 10000978                                                                               |
| :8   |        8 | NUMBER    | 2                                                                                      |
| :9   |        9 | NUMBER    | 1000                                                                                   |
| :10  |       10 | NUMBER    | 0                                                                                      |
========================================================================================================================

TIMESTAMP 卻是16進位.

 

如何讓它變得萌萌噠呢?

如果簡單的16進位換算成10進位 我們可以用WINDOWS的計算機來玩, 可是這是時間啊,ORACLE時間儲存規則不一樣哦.對了其實也是數字儲存方式的

另外還有正負數,小數,指數.

當然我們這裡個時間類型 包含 年份 日期 時間 毫秒.  這四個部分演算法是不一樣的.

你可以SELECT DUMP(SYSDATE) FROM DUAL 出來對比瞭解下

 

囉嗦 上函數. 不夠這函數解決年月日和時間

create or replace function hex_to_date(hexstr in varchar2) return timestamp asbegin  if length(hexstr) <> 14 then    return null;  end if;  return to_timestamp(to_char(to_number(substr(hexstr, 1, 2), 'xx') - 100,                              'fm00') ||                      to_char(to_number(substr(hexstr, 3, 2), 'xx') - 100,                              'fm00') ||                      to_char(to_number(substr(hexstr, 5, 2), 'xx'),                              'fm00') ||                      to_char(to_number(substr(hexstr, 7, 2), 'xx'),                              'fm00') ||                      to_char(to_number(substr(hexstr, 9, 2), 'xx') - 1,                              'fm00') ||                      to_char(to_number(substr(hexstr, 11, 2), 'xx') - 1,                              'fm00') ||                      to_char(to_number(substr(hexstr, 13, 2), 'xx') - 1,                              'fm00'),                      'yyyymmddhh24miss');end;