Histogram endpoint date values to ‘human readable’ date format

Let’s get an endpoint_value for a hight-balanced histogram which represents a date:

select table_name, column_name, endpoint_number, endpoint_value, endpoint_actual_value
from dba_tab_histograms where table_name='BSLN_BASELINES' and column_name='LAST_COMPUTE_DATE'
order by endpoint_number;

Endpoint values for dates will be shown as followed: ‘2457594.23701157’.

The number in front of the dot is the day’s since ’01 JAN -4712′, this is retrievable with the Julian format element ‘J’:

select to_char(to_timestamp('2457594', 'J'), 'DD MON YYYY') endpoint_actual_value from dual; -- 24 JUL 2016
select to_char(to_timestamp('1', 'J'), 'DD MON SYYYY') endpoint_actual_value from dual; -- 01 JAN -4712
select to_char(to_timestamp('5373484', 'J'), 'DD MON YYYY') endpoint_actual_value from dual; -- 31 DEC 9999

The number after the dot is the faction of the day: .5 is noon, 0.25 is six ‘o clock in the morning:

select to_char(trunc(sysdate) + 0.25, 'HH24:MI:SS') endpoint_actual_value from dual; -- 06:00:00
select to_char(trunc(sysdate) + 0.23701157, 'HH24:MI:SS') endpoint_actual_value from dual; -- 05:41:18
select to_char(trunc(sysdate) + 0.87305213, 'HH24:MI:SS') endpoint_actual_value from dual; -- 20:57:12

Combining these to result in the date and time:

select to_char(to_timestamp('2457594', 'J') + .23701157, 'DD MON YYYY HH24:MI:SS') endpoint_actual_value
from dual; -- 24 JUL 2016 05:41:18

Now to get this out of the endpoint_value, we need to find the dot with instr() or assume it’s at the 8th position:

select table_name, column_name, endpoint_number, endpoint_value,
to_char(to_timestamp(substr(endpoint_value,0,7), 'J') + 
    nvl(substr(endpoint_value,8),0), 'DD-MM-YYYY HH24:MI:SS') endpoint_actual_value
from dba_tab_histograms where table_name='BSLN_BASELINES' and column_name='LAST_COMPUTE_DATE'
order by endpoint_number;

But assuming it’s on the 8th position will give problems when a date is before 07 NOV 1975:

select to_char(to_timestamp('1000000', 'J'), 'DD MON YYYY') endpoint_actual_value from dual; -- 07 NOV 1975
select to_char(to_timestamp('999999', 'J'), 'DD MON YYYY') endpoint_actual_value from dual; -- 06 NOV 1975

When we take into account endpoint values with variable length, the calculation gets non-readable:

select table_name, column_name, endpoint_number, endpoint_value, 
to_char(to_timestamp(substr(endpoint_value,0,decode(instr(endpoint_value,'.'),0,7,instr(endpoint_value,'.')-1)), 'J') + 
    nvl(substr(endpoint_value,decode(instr(endpoint_value,'.'),0,8,instr(endpoint_value,'.'))),0), 'DD-MM-YYYY HH24:MI:SS') endpoint_actual_value
from dba_tab_histograms where table_name='BSLN_BASELINES' and column_name='LAST_COMPUTE_DATE' order by endpoint_number;

If anyone has a more elegant solution, please paste it in the comments…

Happy de peppie…