diligent diligent - 1 year ago 54
SQL Question

how to get avg time in postgresql

I have table like this:

name number total_time avg_time
tom 10 00:02:28 ?
kevin 6 09:22:25 ?

total_time is type of interval, I can easily change to string.
now, I want to get the avg_time like this

avg_time = total_time / number;

I wander how to get the result ? I try to calculate day, hour, min, sec, but it's too difficult , anyone who can give me some advice ? thanks a lot

Answer Source

Hopefully this example (based on yours) is helpful:

postgres=# SELECT *, total_time / number AS avg_time 
    FROM (SELECT INTERVAL '00:02:28' AS total_time, 10 AS number) a;
 total_time | number |  avg_time  
 00:02:28   |     10 | 00:00:14.8
(1 row)

Can you be more specific about what part of this isn't working for you?