I have a table in which I am trying to sum times on the same column. I have a column where I log all time entries of any event. So, I would like to group them by gateway ID and have the value in hours, minutes and seconds. I have tried to used timestampdiff, but it takes three arguments and since I have only one row, I couldn't find a way. Is that possible? My table looks like this:
+----+---------+---------------------+
| id | gateway | timestamp |
+----+---------+---------------------+
| 1 | 1 | 2018-03-21 08:52:51 |
| 2 | 1 | 2018-03-21 08:52:54 |
| 3 | 1 | 2018-03-21 08:52:58 |
| 4 | 1 | 2018-03-21 08:53:11 |
| 5 | 2 | 2018-03-21 08:53:51 |
| 6 | 1 | 2018-03-21 08:54:21 |
| 7 | 2 | 2018-03-21 08:54:32 |
| 8 | 2 | 2018-03-21 08:54:44 |
| 9 | 2 | 2018-03-21 08:54:53 |
| 10 | 2 | 2018-03-21 08:55:01 |
+----+---------+---------------------+
basically I would like to group all records by their gateway and then have the sum of time there.
Thanks in advance!
I think you want:
select gateway,
timestampdiff(seconds, min(timestamp), max(timestamp))
from t
group by gateway;
I'm a little confused by your question, because timestampdiff() takes three arguments, not two.