How to limit a SUM using postgres-CodePudding

I want to limit the SUM to 2 hours in postgres sql. I dont want to limit the result of the sum, but the value that it is going to sum.

For example, the following table:

In this case, if I SUM('02:20', '01:50', '00:30', '03:00') the result would be 07:30.

|CODE |  HOUR |
| --- | ----  |
| 1   | 02:20 |
| 2   | 01:50 |
| 3   | 00:30 |
| 4   | 03:00 |

But what i want, is to limit the column HOUR to 02:00. So if the value is > 02:00, it will be replaced with 02:00, only in the SUM.

So the SUM should look like this ('02:00', '01:50', '00:30', '02:00'), and the result would be 06:20

CodePudding user response：

Use case as an argument of the function:

select sum(case when hour < '2:00' then hour else '2:00' end)
from my_table

Test it in Db<>fiddle.

CodePudding user response：

It's still not perfect, but the idea is this:

    select sum(x.anything::time)
    from (select id, 
                 time,
                 case when time <= '02:00' then time::text 
                 else '02:' || (EXTRACT(MINUTES FROM time::time)::text) 
                 end as anything
         from time_table) x