Compute the SUM of Time columns in the Postgres

Question

In my attendance table, the Overtime is mentioned in the format 'hh:mm'. Its String column in the database. Full month Overtime is entering as a single entry. So my column data is as follows

OVERTIME
'52:00'
'30:00'
'98:00'
'06:00'

Now how can get the SUM of the OverTime column.

I have tried SELECT SUM(OVERTIME::TIME) FROM ATTENDANCE. Its giving me the error :

ERROR: date/time field value out of range: "52:00"

time can only be used for times of the day (max. 24 hours). Try with interval instead. — sticky bit
– sticky bit, Commented Jul 23, 2018 at 13:00
@stickybit. I'm new with INTERVAL, Can you give an example to use Interval in this scenario, pls. — Sajeev
– Sajeev, Commented Jul 23, 2018 at 13:05
Just replace ::time with ::interval to cast to an interval rather than to a time. And consider changing the column's type to interval if possible. — sticky bit
– sticky bit, Commented Jul 23, 2018 at 13:08

Gordon Linoff · Accepted Answer · 2018-07-23 13:06:09Z

1

Assuming the values are HH:MM, then you can use string manipulations:

select sum(left(overtime, 2)::numeric + right(overtime, 2)::numeric/60) as hours

Note that this will give the results as decimal hours.

If you want the values as an interval, you can also do that:

select sum( cast(overtime as interval) )

Note that this will give the results as days, hours, and minutes.

answered Jul 23, 2018 at 13:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Eric Aya · Accepted Answer · 2023-01-17 15:23:11Z

0

The other solution didn't it in my case because I have a mix of formats i-e H:MM or HH:MM. This is what worked for me:

select sum(SPLIT_PART(overtime,':',1)::numeric + split_part(overtime,':',1)::numeric/60) as hours from your_table

edited Jan 17, 2023 at 15:23

Eric Aya

70.2k36 gold badges190 silver badges266 bronze badges

answered Jan 17, 2023 at 12:11

Faraz Irfan

1,3263 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

Compute the SUM of Time columns in the 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