Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org, "Frank F(dot) Burmo" <fburmo(at)online(dot)no>
Subject: Re: BUG #2994: avg() calculates wrong on Interval-type
Date: 2007-02-13 00:00:34
Message-ID: 16483.1171324834@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Peter Eisentraut <peter_e(at)gmx(dot)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

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2007-02-13 02:01:19
Subject: Re: [BUGS] wrong behavior using to_char()
Previous:From: Anthony TaylorDate: 2007-02-12 23:59:31
Subject: BUG #2996: to_char( timestamp, 'DD-Mon-YYYY HH24:MI:SS.MS' ) reports .1000 ms

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group