If the intervals are all expressed in seconds then sure, the calculation
is straightforward and useful. I'm wondering what happens when nonzero
values of days and months get in there.
regards, tom lane
The existing logic used in avg(interval) can be seen in backend/utils/adt/timestamp.c, refer to functions interval_accum, interval_avg and interval_div.
interval_div(interval, double) is the most interesting for this discussion. There is a helpful comment that reads /* evaluate fractional months as 30 days */.
So for example, interval_div('4 mons'::interval, 3) gives you '1 mon 10 days'.
It's not perfect, but doing arithmetic that involves converting between months and days never is. All in favour of deleting the month as unit of measurement of time say "aye".
Well that's not going to happen in my lifetime. How about we just extend this same logic over to stddev and variance? It's strange having avg but not the other two.
Regards,
BJ