BUG #2994: avg() calculates wrong on Interval-type

The following bug has been logged online:

Bug reference: 2994
Logged by: Frank F. Burmo
Email address: fburmo(at)online(dot)no
PostgreSQL version: 8.1.4
Operating system: i386-portbld-freebsd6.1
Description: avg() calculates wrong on Interval-type
Details:

The following avg()-call gives me a result of: "2 days, 27:53:49.359573"...
which must be wrong. There are only 24 hours in a day.

SQL:

--
-- PostgreSQL database dump
--

-- Started on 2007-02-12 12:10:10

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1398 (class 1259 OID 30398)
-- Dependencies: 1
-- Name: temp_supporttime; Type: TABLE; Schema: public; Owner: -;
Tablespace:
--

CREATE TABLE temp_supporttime (
pid bigint,
supportcaseid bigint,
started timestamp with time zone,
finished timestamp with time zone,
"interval" interval
);

--
-- TOC entry 1724 (class 0 OID 30398)
-- Dependencies: 1398
-- Data for Name: temp_supporttime; Type: TABLE DATA; Schema: public; Owner:
-
--

INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 75, '2006-10-04 17:21:51.979709+02', '2006-10-04
17:26:57.494561+02', '00:05:05.514852');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 75, '2006-10-04 17:21:51.979709+02', '2006-10-04
17:24:27.687663+02', '00:02:35.707954');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 78, '2006-10-20 09:18:35.1616+02', '2006-10-23
17:53:02.136005+02', '3 days 08:34:26.974405');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 79, '2006-10-20 10:26:17.877372+02', '2006-10-20
11:24:00.185209+02', '00:57:42.307837');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 83, '2006-10-26 13:31:13.849678+02', '2006-10-26
13:34:19.641588+02', '00:03:05.79191');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 84, '2006-10-27 20:16:44.092926+02', '2006-10-30
21:59:16.71742+01', '3 days 02:42:32.624494');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 85, '2006-10-30 22:02:35.164633+01', '2006-10-30
22:04:14.285622+01', '00:01:39.120989');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 86, '2006-10-30 22:06:00.24526+01', '2006-10-31
17:01:59.596107+01', '18:55:59.350847');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 87, '2006-11-01 20:04:21.001824+01', '2006-11-01
20:05:13.404754+01', '00:00:52.40293');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 88, '2006-11-01 20:05:53.747059+01', '2006-11-01
20:06:19.056036+01', '00:00:25.308977');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 89, '2006-11-01 20:09:46.329745+01', '2006-11-01
20:11:35.71515+01', '00:01:49.385405');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 90, '2006-11-03 14:23:44.299795+01', '2006-11-18
17:35:57.545909+01', '15 days 03:12:13.246114');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 91, '2006-11-04 09:30:17.948822+01', '2006-11-06
10:35:44.221352+01', '2 days 01:05:26.27253');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 91, '2006-11-04 09:30:17.948822+01', '2006-11-06
16:35:43.498654+01', '2 days 07:05:25.549832');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 92, '2006-11-05 14:21:16.954045+01', '2006-11-06
11:15:54.593218+01', '20:54:37.639173');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 93, '2006-11-09 10:07:52.548302+01', '2006-11-18
17:33:41.823806+01', '9 days 07:25:49.275504');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 94, '2006-11-10 20:06:22.116797+01', '2006-11-18
17:34:38.441316+01', '7 days 21:28:16.324519');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 95, '2006-11-22 09:41:56.827706+01', '2006-11-22
19:43:52.569055+01', '10:01:55.741349');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 96, '2006-11-23 21:10:37.562993+01', '2006-11-24
12:22:59.970675+01', '15:12:22.407682');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 96, '2006-11-23 21:10:37.562993+01', '2006-11-29
19:23:48.655695+01', '5 days 22:13:11.092702');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 97, '2006-11-23 21:12:28.550887+01', '2006-11-24
12:41:20.597025+01', '15:28:52.046138');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 98, '2006-11-23 21:20:30.843546+01', '2006-11-24
12:45:46.838935+01', '15:25:15.995389');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 99, '2006-11-23 21:21:57.20644+01', '2006-11-24
12:52:41.020068+01', '15:30:43.813628');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 100, '2006-11-24 11:11:36.215094+01', '2007-01-02
12:35:17.223123+01', '39 days 01:23:41.008029');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (5, 101, '2006-11-27 12:26:48.157682+01', '2006-11-28
11:26:35.407315+01', '22:59:47.249633');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 102, '2006-11-27 15:22:37.51272+01', '2006-11-27
15:29:56.8134+01', '00:07:19.30068');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 104, '2006-12-04 10:30:24.756294+01', '2006-12-04
10:37:51.214107+01', '00:07:26.457813');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 105, '2006-12-05 09:47:31.247568+01', '2006-12-05
15:51:01.192744+01', '06:03:29.945176');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 106, '2006-12-07 10:07:11.73995+01', '2006-12-12
21:02:07.983707+01', '5 days 10:54:56.243757');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 107, '2006-12-07 10:57:24.629538+01', '2006-12-08
12:52:13.446522+01', '1 day 01:54:48.816984');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 108, '2006-12-07 18:52:50.220324+01', '2006-12-13
11:18:11.568496+01', '5 days 16:25:21.348172');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 109, '2006-12-08 16:00:31.970861+01', '2007-01-01
15:03:45.049743+01', '23 days 23:03:13.078882');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 110, '2006-12-18 08:53:02.721715+01', '2006-12-18
13:04:12.548722+01', '04:11:09.827007');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 111, '2006-12-19 13:37:17.045201+01', '2006-12-19
14:42:16.835872+01', '01:04:59.790671');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 112, '2007-01-01 14:36:35.64653+01', '2007-01-02
13:03:31.050025+01', '22:26:55.403495');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 113, '2007-01-01 14:39:56.778301+01', '2007-01-02
13:03:39.08025+01', '22:23:42.301949');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 114, '2007-01-01 14:45:15.339681+01', '2007-01-02
12:24:05.313185+01', '21:38:49.973504');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 115, '2007-01-16 21:42:40.063679+01', '2007-01-24
06:06:59.318571+01', '7 days 08:24:19.254892');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 116, '2007-01-16 22:00:31.432014+01', '2007-01-24
06:07:13.695724+01', '7 days 08:06:42.26371');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 117, '2007-01-16 22:03:33.32909+01', '2007-01-16
22:07:49.557595+01', '00:04:16.228505');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 118, '2007-01-17 10:06:08.340201+01', '2007-01-17
10:06:44.73304+01', '00:00:36.392839');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 119, '2007-01-22 13:22:15.826197+01', '2007-01-22
15:02:48.939547+01', '01:40:33.11335');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 119, '2007-01-22 13:22:15.826197+01', '2007-01-25
11:36:41.502404+01', '2 days 22:14:25.676207');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 121, '2007-01-22 15:54:57.832479+01', '2007-01-22
16:59:45.704911+01', '01:04:47.872432');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 122, '2007-01-23 11:03:09.106842+01', '2007-01-23
12:51:05.613916+01', '01:47:56.507074');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 123, '2007-01-24 09:07:37.498273+01', '2007-01-24
15:35:27.187838+01', '06:27:49.689565');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 124, '2007-01-24 09:56:35.943598+01', '2007-01-26
10:38:20.798455+01', '2 days 00:41:44.854857');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 125, '2007-01-24 10:05:59.94873+01', '2007-01-24
22:05:38.785341+01', '11:59:38.836611');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 126, '2007-01-24 13:13:05.429093+01', '2007-02-01
13:20:09.964053+01', '8 days 00:07:04.53496');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 127, '2007-01-26 10:16:22.784768+01', '2007-01-26
11:39:49.898514+01', '01:23:27.113746');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 129, '2007-01-28 19:30:41.309661+01', '2007-01-29
10:57:58.903073+01', '15:27:17.593412');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 130, '2007-01-29 09:22:27.015735+01', '2007-02-07
12:25:11.699774+01', '9 days 03:02:44.684039');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 131, '2007-01-29 12:42:10.534149+01', '2007-01-30
12:47:43.810359+01', '1 day 00:05:33.27621');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 132, '2007-01-29 12:52:39.264692+01', '2007-02-07
12:25:40.550089+01', '8 days 23:33:01.285397');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 133, '2007-01-29 15:36:35.608598+01', '2007-01-30
11:39:48.890482+01', '20:03:13.281884');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 134, '2007-01-30 11:32:32.075055+01', '2007-01-30
12:47:05.566399+01', '01:14:33.491344');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 135, '2007-01-30 12:40:16.460654+01', '2007-02-06
10:10:20.270662+01', '6 days 21:30:03.810008');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 136, '2007-01-30 15:27:00.734854+01', '2007-01-30
17:08:34.432176+01', '01:41:33.697322');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 137, '2007-01-31 10:19:27.890481+01', '2007-02-08
10:25:44.187551+01', '8 days 00:06:16.29707');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 138, '2007-01-31 12:47:25.846261+01', '2007-01-31
12:56:07.722464+01', '00:08:41.876203');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 139, '2007-01-31 12:59:45.587041+01', '2007-01-31
16:23:28.720459+01', '03:23:43.133418');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 140, '2007-02-01 09:01:59.994751+01', '2007-02-06
10:14:36.975396+01', '5 days 01:12:36.980645');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 141, '2007-02-01 15:19:58.313032+01', '2007-02-01
15:44:21.371882+01', '00:24:23.05885');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 142, '2007-02-01 16:37:06.32688+01', '2007-02-02
14:54:31.766998+01', '22:17:25.440118');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 143, '2007-02-01 21:13:09.126239+01', '2007-02-01
21:15:16.154263+01', '00:02:07.028024');
INSERT INTO temp_supporttime (pid, supportcaseid, started, finished,
"interval") VALUES (11, 144, '2007-02-02 15:22:20.506979+01', '2007-02-07
17:01:56.32519+01', '5 days 01:39:35.818211');

-- Completed on 2007-02-12 12:10:11

--
-- PostgreSQL database dump complete
--

-- Query that gives wrong result:

SELECT AVG(interval) FROM temp_supporttime

-- the output is 2 days, 27:53:49.359573
-- but there are only 24 hours in a day...