Precision of aggregate function on 'INTERVAL HOUR TO MINUTE' datatype in SQL

Question

I'm running a very small database that contains a table with a column containing data of type INTERVAL HOUR TO MINUTE. Although this means the table will only store time intervals with minute precision, the database system I am using (PostgreSQL) will return an interval with microsecond precision on a aggregate function such as AVG(). Can I rely on this behavior, or is it possible that in the future the database system will return values with only minute precision? How do other DBMS's behave in this respect?

I'm asking because values in the table do not require finer than minute precision, but I expect higher precision when I use an aggregate function.

Answer 1

An aggregate function such as avg() has to return the general form of an interval, as the average of multiple values can lie in between. This will definitely not change in future releases. Also, the datatypes are identical internally. Just the least significant parts get truncated.

The behavior is similar with other datatypes. If you compute an average over an integer column, you get a result of type numeric that can hold exact results.

If you want the results to be truncated (not your request), you can always cast to interval hour to minute explicitly to be sure.

SELECT avg(i)::interval hour to minute from mytbl;

I can't say much about other RDBMSes. Maybe additional answers can fill in here?