MySQL: как SUM() TIMEDIFF() в группе?

Ответ 1

Использование:

  SELECT t.user_id,       
         SEC_TO_TIME(SUM(TIME_TO_SEC(t.endtime) - TIME_TO_SEC(t.starttime))) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id

Шаги:

• Используйте TIME_TO_SEC для преобразования TIME в секунды для математической операции.
• Суммируйте разницу
• Используйте SEC_TO_TIME, чтобы преобразовать секунды в TIME

Основываясь на данных образца, я бы просто предложил:

  SELECT t.user_id,       
         TIMEDIFF(MIN(t.startdate), MAX(t.enddate)) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id   

ПРИМЕЧАНИЕ. В этом коде есть ошибка, если вы используете datetime. TIME_TO_SEC только преобразует временную секцию, поэтому вы получаете большой негативы, если часы пройдут за полночь. Вместо этого используйте UNIX_TIMESTAMP чтобы сделать сумму. Также SEC_TO_TIME высвечивается при значениях, больших 3020399 секунд, например. SELECT TIME_TO_SEC (SEC_TO_TIME (3020400)); если ты см. это значение 838: 59: 59, вы достигли максимума и, возможно, просто нуждаетесь для разделения на 3600, чтобы просто показать часы.

Ответ 2

AFAIK, ваш единственный вариант - вывести на UNIX_TIMESTAMP и выполнить некоторые целочисленные вычисления, заменив случайную дату (я выбрал 2000-01-01) для столбцов TIME без даты.

SELECT TIMEDIFF(
    DATE_ADD('2000-01-01 00:00:00',
       INTERVAL 
       SUM(UNIX_TIMESTAMP(CONCAT('2000-01-01 ',TimeDiff)) - UNIX_TIMESTAMP('2000-01-01 00:00:00')
       SECOND),
    '2000-01-01 00:00:00')
FROM MyTable;

Потому что может показаться, что вы можете SUM столбцы TIME, но на самом деле они будут отбрасываться на неприятные целые числа или плавающие элементы, которые не будут соответствовать спецификациям времени (попробуйте с суммой минут > 60, и вы увидите, что я имею в виду).

Для тех, кто утверждает, что вы можете SUM столбцы времени:

mysql> create table timetest(a TIME);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO timetest VALUES ('02:00'),('03:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SUM(a) FROM timetest;
+--------+
| SUM(a) |
+--------+
|  50000 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| 05:00:00     |
+--------------+
1 row in set (0.00 sec)

mysql> -- seems ok, but wait
mysql> INSERT INTO timetest VALUES ('02:30');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| 07:30:00     |
+--------------+
1 row in set (0.00 sec)

mysql> -- and now, oh ye unbelievers:
mysql> INSERT INTO timetest VALUES ('01:40');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| NULL         |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> -- why is that? because it uses integer arithmetic, not time - arithmetic:
mysql> SELECT SUM(a) FROM timetest;
+--------+
| SUM(a) |
+--------+
|  87000 |
+--------+
1 row in set (0.00 sec)

mysql> -- that cannot be cast to time

Ответ 3

Будет ли он работать для вашего?

SELECT User_ID, TIME(SUM(TIMEDIFF(EndTime, StartTime))) AS TimeDiff
FROM MyTable GROUP BY User_ID

Ответ 4

Я предлагаю вам вместо TO_SECONDS:

SELECT t.user_id,       
         SEC_TO_TIME(SUM(TO_SECONDS(t.endtime) - TO_SECONDS(t.starttime))) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id

Ответ 5

Это сработало для меня в приложении для отслеживания времени. Короче говоря, я преобразовал все интервалы в секунды, добавил их и преобразовал обратно в формат TIME. Я новичок, поэтому прошу прощения за любую неуклюжесть в коде. Я всегда приветствую отзывы.

СОЗДАТЬ таблицу time_se сегментов (сегмент_идентификатор int не равен нулю первичный ключ auto_increment, sign_in DATETIME, sign_out DATETIME, секунды INT не равны NULL, время отображения не равно NULL, INT__пользователя INT);

ОБНОВЛЕНИЕ time_segments SET Seconds = TIME_TO_SEC (-TIMEDIFF (sign_in, sign_out));

ОБНОВЛЕНИЕ time_segments SET Display = SEC_TO_TIME (секунды);

INSERT INTO time_segments (sign_in, sign_out, user_id) VALUES ('2019-03-12 14:01:00', '2019-03-12 16:45:00', 1),...;

mysql> select * from time_segments;

| сегмент_ид | вход | выход | Секунды | Дисплей |

| 1 | 2019-03-12 14:01:00 | 2019-03-12 16:45:00 | 9840 | 02:44:00 |

mysql> SELECT SEC_TO_TIME (SUM (Seconds)) AS "Часы периода оплаты" FROM time_segments;

| Payperiod Часы | + ----------------- + | 49:29:00 |

Ответ 6

Если вы столкнулись с этой ошибкой, хорошей альтернативой является использование TIMESTAMPDIFF, а затем получение суммы столбца, который вы создаете.

select(sum(df.elapse)/60 as diff
from( SELECT c1.start, c1.end, TIMESTAMPDIFF(MINUTE,c1.start,c1.end) as elapse
from c1) df

Примечание предостережения: вы приблизитесь к фактическому количеству часов, но, возможно, от него останется несколько десятичных знаков. Смотрите округление с плавающей запятой для более подробной информации.