Search code examples
mysqlsqldatetimemaxmin

Get time difference during a Grouped by query in MySQL

How can I make a difference to get time between first and last line during my SQL query ?

Datas are the following ones:

|--------|-----------|---------------------|
| EST_Id | EST_Token | EST_Datetime        |
|--------|-----------|---------------------|
|      1 |     vexef | 2020-10-17 16:13:01 |
|      2 |     vexef | 2020-10-17 16:13:21 |
|      3 |     vexef | 2020-10-17 16:14:31 |
|      4 |     vexef | 2020-10-17 16:13:51 |
|      5 |     fardd | 2020-10-17 17:00:11 |
|      6 |     fardd | 2020-10-17 17:00:17 |
|      7 |     fardd | 2020-10-17 17:00:19 |
|--------|-----------|---------------------|

For example for the token vexef I should have 50 seconds.

This is what I have tried:

SELECT *, TIMEDIFF(MAX(EST_Datetime), MIN(EST_Datetime))  AS diff FROM table GROUP BY EST_Token ORDER BY EST_Id ASC

The query looks working but diff returns me 0.

Thanks.

Solution

  • You seem to want aggregation:

    select est_token, timestampdiff(second, min(est_datetime), max(est_datetime)) diff
from mytable
group by est_token

    This gives you one row per est_token, with the difference between the earliest and latest est_datetime, expressed in seconds.