Thread: Statistical aggregates with intervals

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!

Thomas,

On Wed, Aug 22, 2012 at 12:25 PM, Thomas Munro <munro@ip9.org> wrote:
> I noticed that 'avg' works on 'interval', but 'stddev' and 'variance' don't:

I don't know why, but you could convert 'interval' into something else
where all the functions work:

CREATE OR REPLACE FUNCTION interval_to_seconds(interval)
RETURNS double precision AS $$
    SELECT (extract(days from $1) * 86400)
         + (extract(hours from $1) * 3600)
         + (extract(minutes from $1) * 60)
         + extract(seconds from $1);
$$ LANGUAGE SQL;

Cheers,

Chris
--
Christopher Swingley
Fairbanks, Alaska
http://swingleydev.com/
cswingle@gmail.com

Hi,

On 24 August 2012 07:39, Christopher Swingley <cswingle@gmail.com> wrote:
> I don't know why, but you could convert 'interval' into something else
> where all the functions work:
>
> CREATE OR REPLACE FUNCTION interval_to_seconds(interval)
> RETURNS double precision AS $$
>     SELECT (extract(days from $1) * 86400)
>          + (extract(hours from $1) * 3600)
>          + (extract(minutes from $1) * 60)
>          + extract(seconds from $1);
> $$ LANGUAGE SQL;

Looks complicated. You can extract 'epoch':
db=# select now() - (now() - interval '1 day');
 ?column?
----------
 1 day
(1 row)

db=# select extract(epoch from (now() - (now() - interval '1 day')));
 date_part
-----------
     86400
(1 row)


--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)