Getting HH:mm version of total minutes in SQL

Question

I have a column which is a sum of total minutes users spend on a task. This column was got using:

(sum(cast(LTRIM(DATEDIFF(SECOND, 0, TotalTimeOtherTasks))as int)) over (partition by headLogs.operatorID)/60) as TotalTimeOtherTasks

This returns e.g 315. TotalTimeOtherTasks is a column of times for that task e.g 00:30:23 01:05:55

I want to make this show in HH:mm format so, 315 = 05:15.

I've tried doing other answers for similar questions such as; using separate formulas and concatenating them but i get errors such as " '05:15' cannot be converted to an int " once it gets the answer or "':' is not recognized as part of the formula". If i convert it I then can't use sum(). So i think this might be its own question.

Database is Oracle, SQLServer, MySQL ... ? also please provide a small example table and expected result. Not only one example(like you did) but 2 or 3 data in the table (with TotalTimeOtherTasks and operatorID columns) and your expected results... — VBoka, Jun 05 '20 at 10:12

score 0 · Accepted Answer · answered Jun 05 '20 at 10:14

0

One method is to compute the minutes and seconds separately, and then concatenante them

concat(
    sum(datediff(second, 0, TotalTimeOtherTasks)) 
        over (partition by headLogs.operatorID) / 60,
    ':',
    sum(datediff(second, 0, TotalTimeOtherTasks)) 
        over (partition by headLogs.operatorID) % 60
) as TotalTimeOtherTasks

Note that I removed the casting, that seems unnecessary here: in most databases, datediff() returns a integer already.

answered Jun 05 '20 at 10:14

GMB

216,147
25
84
135

that done it, thanks! Think I was just getting confused by all the examples I've seen and not executing them properly. – Roy1895 Jun 05 '20 at 10:23

silentsudo · Answer 2 · 2020-06-05T10:36:34.300

0

Though the answer is already accepted, I will post my solutions which is working in MySQL. Reference from here: Convert number of minutes to hh:mm

Assuming the following table structure

create table total_minutes(
    id bigint(20) NOT NULL AUTO_INCREMENT,
    total_time int,
    primary key(id)
);

SELECT total_time, floor(total_time/60) as hours, lpad(floor(total_time%60), 2, '0') as minutes,
concat(floor(total_time/60), ":", lpad(floor(total_time%60), 2, '0')) as hh_mm FROM classifiedads.total_minutes;

Gives

t   h   m   hh:mm
35  0   35  0:35
95  1   35  1:35
69  1   9   1:09

edited Jun 05 '20 at 10:36

answered Jun 05 '20 at 10:28

silentsudo

6,730
6
39
81

1

I think they are using SQL Server (based on the `DATEDIFF()` syntax in the question). – GMB Jun 05 '20 at 10:35

Getting HH:mm version of total minutes in SQL

2 Answers2