Re: [GENERAL] Statistical aggregates with intervals
Thomas, On Wed, Aug 22, 2012 at 12:25 PM, Thomas Munro 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/ cswin...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Statistical aggregates with intervals
Hi, On 24 August 2012 07:39, Christopher Swingley 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.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
