Hi
I noticed that 'avg' works on 'interval', but 'stddev' and 'variance' don't:
hack=> create table test (start_time timestamptz, end_time timestamptz);
CREATE TABLE
hack=> insert into test values (now(), now() + interval '1 second');
INSERT 0 1`
hack=> insert into test values (now(), now() + interval '1 second');
INSERT 0 1
hack=> insert into test values (now(), now() + interval '4 second');
INSERT 0 1
hack=> select avg(end_time - start_time) from test;
avg
----------
00:00:02
(1 row)
hack=> select stddev(end_time - start_time) from test;
ERROR: function stddev(interval) does not exist
LINE 1: select stddev(end_time - start_time) from test;
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
Sure enough the standard deviation of time intervals can be computed
by following that hint:
hack=> select interval '1 second' * stddev(extract(epoch from end_time
- start_time)) as stddev from test;
stddev
-----------------
00:00:01.732051
(1 row)
But is there some way I can use CREATE AGGREGATE to define stddev for
intervals in terms of the built-in stddev aggregate, just transforming
the inputs and output? Or am I missing something fundamental that
explains why stddev(interval) isn't supported?
Thanks!
