How to limit a SUM using postgres

Question

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

klin · Accepted Answer · 2022-02-22 15:22:37Z

3

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.

answered Feb 22, 2022 at 15:22

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Diego Lobo · Accepted Answer · 2022-02-22 16:13:00Z

0

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

answered Feb 22, 2022 at 16:13

Diego Lobo

4643 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to limit a SUM using postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related