MySQL：将分钟转换为小时和分钟

I have a MySQL database. One of it's table(table name is places) has a column named duration. I have separated the duration column in to hours and minutes.

我有一个MySQL数据库。其中一个表(表名是places)有一个名为duration的列。我已将持续时间列分为小时和分钟。

|--------------
|Duration
|--------------
|Hours |mins
---------------
| 1    | 30
| 1    | 40
| 0    | 30
| 3    | 40
----------------------
Total   | 5    | 140

But when I get total of this values it gives 5 in hours column and 140 in mins column But I want to have 7 in hours column and 20 in minites column

但是当我得到这个值的总和时,它给出了5个小时的列和140个分钟的列但是我希望有7个小时的列和20个小的列

My query is

我的疑问是

$query3a = mysql_query("select SUM(hours) AS Totalhours, SUM(mins) AS Totalmins  from places");
while ($query4a = mysql_fetch_array($query3a))

enter code here

echo "<tr bgcolor='#87CEFA'><td>Total</td><td></td><td>".$query4a['Totaldistance']."&nbsp";
echo "Km";
echo "</td><td>".$query4a['Totalhours']."&nbsp";
echo "hrs";
echo "</td><td>".$query4a['Totalmins']."&nbsp";
echo "mins";

2 个解决方案

#1

You need to sum, and convert every 60 minutes to an hour:

你需要求和,每60分钟转换一小时:

SELECT SUM(hours) + SUM(mins) DIV 60 AS Totalhours, 
       SUM(mins) MOD 60 AS Totalmins  
FROM   places

#2

Add hours and minutes in term of hours to get total hours add minutes and get reminder of it to get total minutes

按小时数添加小时和分钟,以获得总小时数添加分钟数,并提醒它以获得总分钟数

so your sql query should be like

所以你的SQL查询应该是这样的

SELECT SUM(hours) + SUM(mins) / 60 as TotalHours, SUM(mins) % 60 as TotalMins FROM places

选择SUM(小时)+ SUM(分钟)/ 60作为TotalHours,SUM(分钟)%60作为TotalMins FROM场所

#1