我想要像 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 the TIME 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屋！