The TIMESAMPE_HEX_TO_CHAR function solves the problem of time hexadecimal visualization.

The TIMESAMPE_HEX_TO_CHAR function solves the problem of time hexadecimal visualization.

11 GB provides an SQL view that exceeds 4 seconds. It contains a lot of necessary information and can be used at the same time.

-- View the statement execution plan for more than 4 seconds
SELECT dbms_sqltune.report_ SQL _monitor (SQL _id => '& SQL _id', report_level => 'all', type => 'text') comm FROM dual;

Get a formatting item

 

However

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 |
========================================================== ========================================================== ==========================================================

The TIMESTAMP is in hexadecimal format.

 

How can we make it cute?

If the simple hexadecimal format is converted into a 10-hexadecimal format, we can use the WINDOWS Calculator to play the game. However, this is time, and the ORACLE time storage rules are different. By the way, it is actually a digital storage method.

There are also positive and negative numbers, decimals, and indexes.

Of course, the time type here contains the year date time in milliseconds. These four algorithms are different.

You can select dump (SYSDATE) from dual.

 

The function is not enough to solve the problem of year, month, day, and time.

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;