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

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

From

"Frank F. Burmo"

Date:

12 February 2007, 16:52:58

The following bug has been logged online:

Bug reference:      2994
Logged by:          Frank F. Burmo
Email address:      fburmo@online.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...

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

From

Tom Lane

Date:

12 February 2007, 20:15:02

"Frank F. Burmo" <fburmo@online.no> writes:
> 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.

That's a false premise.  Days and hours/minutes/seconds are independent
components of an interval.  Some days have 23 or 25 hours, therefore
e.g. "48 hours" is not interchangeable with "2 days".  If you want to
assume it is, see justify_hours().

            regards, tom lane

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

From

Peter Eisentraut

Date:

12 February 2007, 22:19:04

Tom Lane wrote:
> "Frank F. Burmo" <fburmo@online.no> writes:
> > 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.
>
> That's a false premise.  Days and hours/minutes/seconds are
> independent components of an interval.

I don't understand how it does the computation then.  None of his input
data contains hour/minute/second components in excess of 24 hours, so
to arrive at 27-some hours it must have pulled over some days.

Moreover, my system thinks that the average of '1 day 12 hours' and '8
hours' is 22 hours, and also that the average of '2 days' and '3 days'
is 2 days and 12 hours, so it does make the assumption that 1 day = 24
hours.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

From

Tom Lane

Date:

12 February 2007, 23:01:03

Peter Eisentraut <peter_e@gmx.net> writes:
> Moreover, my system thinks that the average of '1 day 12 hours' and '8
> hours' is 22 hours, and also that the average of '2 days' and '3 days'
> is 2 days and 12 hours, so it does make the assumption that 1 day = 24
> hours.

Yeah, that's what it does with fractional days, because it doesn't have
a lot of choice --- we could perhaps change the day field from integer
to fractional, but I don't know what 0.5 day really means, so I'm not
sure that that'd be an improvement.  What it won't do is up-convert
hours to days without being told to (via justify_hours).

The issue in its simplest form is:

regression=# select '3 days'::interval / 2;
    ?column?
----------------
 1 day 12:00:00
(1 row)

regression=# select '72 hours'::interval / 2;
 ?column?
----------
 36:00:00
(1 row)

regression=# select '3 days'::interval * 2;
 ?column?
----------
 6 days
(1 row)

regression=# select '72 hours'::interval * 2;
 ?column?
-----------
 144:00:00
(1 row)

            regards, tom lane