Tag Archives: Statistics

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

Continue reading →