Standard deviation function in oracle for DateTime format

Question

How to use standard deviation for Time format. I have column with DateTime data and I want use standard deviation function but I don't have same result like when I use this data in excel.

Answer 1

Apparently Oracle didn't implement average and standard deviation for dates (which is strange - they make perfect sense). Instead, you need to subtract a fixed date (SYSDATE is a perfect candidate); for standard deviation you don't need to add anything back, since subtracting a constant from all members of a sample does not change the standard deviation. If you wanted to compute the average, you would add SYSDATE back to the result. The standard deviation is in days; multiply by 24 if you need it in hours, or 86400 if you need it in seconds, etc.

Example:

with input_dates (dt) as (
        select to_date ('2016/03/20 13:30:31', 'yyyy/mm/dd hh24:mi:ss') from dual union all
        select to_date ('2016/03/22 03:14:32', 'yyyy/mm/dd hh24:mi:ss') from dual union all
        select to_date ('2016/03/30 09:12:43', 'yyyy/mm/dd hh24:mi:ss') from dual union all
        select to_date ('2016/04/02 19:22:35', 'yyyy/mm/dd hh24:mi:ss') from dual 
     )
select stddev(0+to_number(sysdate - dt)) as sample_sd, 
       stddev_pop(dt - sysdate)          as population_sd
from   input_dates;

Output:

 SAMPLE_SD POPULATION_SD
---------- -------------
6.39233719    5.53592639

In Excel:

         3/20/2016 13:30
          3/22/2016 3:14
          3/30/2016 9:12
          4/2/2016 19:22
STDEVA       6.392417475
STDEV.P      5.535995925

The results are indeed slightly different; this is probably due to rounding, both in translating dates to numbers and in taking square roots and such.