MySQL timestampdiff of records on same column

Question

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!

Edit your question and show the results that you want. – Gordon Linoff Mar 21 '18 at 11:59 — Gordon Linoff, Mar 21 '18 at 11:59

score 1 · Accepted Answer · answered Mar 21 '18 at 12:01

1

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.

answered Mar 21 '18 at 12:01

Gordon Linoff

1,242,037
58
646
786

MySQL timestampdiff of records on same column

1 Answers1