How to sum the minutes of each activity in Postgresql?

The column “activitie_time_enter” has the times.

The column “activitie_still” indicates the type of activity.

The column “activitie_walking” indicates the other type of activity.

Table example:

activitie_time_enter | activitie_still | activitie_walking
           17:30:20  |        Still    | 
           17:31:32  |        Still    |
           17:32:24  |                 |  Walking  
           17:33:37  |                 |  Walking  
           17:34:20  |        Still    | 
           17:35:37  |        Still    |
           17:45:13  |        Still    |
           17:50:23  |        Still    |
           17:51:32  |                 |  Walking

What I need is to sum up the total minutes for each activity separately. Any suggestions or solution?

Answer

First calculate the duration for each activity (the with CTE) and then do conditional sum.

with t as
(
 select
   *, lead(activitie_time_enter) over (order by activitie_time_enter) - activitie_time_enter as duration
 from _table 
)
select 
    sum (duration) filter (where activitie_still = 'Still') as total_still, 
    sum (duration) filter (where activitie_walking = 'Walking') as total_walking 
from t;

/** Result:
total_still|total_walking|
-----------+-------------+
   00:19:16|     00:01:56|
*/

BTW do you really need two columns (activitie_still and activitie_walking)? Only one activity column with those values will do. This will allow more activities (Running, Sleeping, Working etc.) w/o having to change the table structure.