我想要像 sum() 这样的函数在 mysql 中添加数据类型 time 列中的所有行 [英] I want function like sum() to add the all rows in a column of data type time in mysql
问题描述
在我的数据库中有如下表
+----+-----------+---------+|身份证 |fk_id |持续时间 |+----+-----------+-----------+|1 |23 |00:00:31 ||2 |23 |00:00:36 ||3 |第677话00:00:36 ||4 |第678话00:00:36 |+----+-----------+-----------+
在上表中,列持续时间的数据类型为 time .
下面是那个表的schema
创建表:CREATE TABLE `durationof` (`id` int(11) NOT NULL AUTO_INCREMENT,`fk_id` int(11) 非空,`持续时间`时间不为空,主键 (`id`))
这里只是我想在查询的持续时间列中添加所有时间,我该怎么做?
就像 sum 函数一样,有没有任何函数可以添加所有 mysql 时间值.
我试过 select addtime(diration) from durationof;
但这行不通.
我完全不同意 @Corbin 的原始答案.如TIME
类型所述:><块引用>
TIME 值的范围可以从 '-838:59:59'
到 '838:59:59'
.小时部分可能会很大,因为 TIME
类型不仅可以用来表示一天中的某个时间(必须小于 24 小时),还可以表示经过的时间或两个事件之间的时间间隔(可能远远超过 24 小时,甚至是负数).
对所有这些间隔求和:转换为秒,取sum 然后 转换又回来了.
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) FROM durationof
在 sqlfiddle 上查看.
In my database there is table as below
+----+-----------+----------+
| id | fk_id | duration |
+----+-----------+----------+
| 1 | 23 | 00:00:31 |
| 2 | 23 | 00:00:36 |
| 3 | 677 | 00:00:36 |
| 4 | 678 | 00:00:36 |
+----+-----------+----------+
In the above table the column duration is of data type time .
The following one is the schema of that table
Create Table: CREATE TABLE `durationof` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fk_id` int(11) NOT NULL,
`duration` time NOT NULL,
PRIMARY KEY (`id`))
Here the thing just I want to add all times in a duration column in a query, how can I?
Just like sum function is there any function to add all mysql time vales .
I tried select addtime(diration) from durationof;
But that is not working.
I completely disagree with @Corbin's original answer. As documented under The TIME
Type:
TIME values may range from
'-838:59:59'
to'838:59:59'
. The hours part may be so large because theTIME
type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
To take the summation of all such intervals: convert to seconds, take the sum and then convert back again.
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) FROM durationof
See it on sqlfiddle.
这篇关于我想要像 sum() 这样的函数在 mysql 中添加数据类型 time 列中的所有行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!