Calculate the time difference between two timestamps in mysql

Question

I work on a MySQL database table that has a column containing timestamps (Ex. 2014-09-16 09:08:05) of the time I ping different hosts. My question is how could I calculate in minutes the difference between the first ping and the last one for specific hosts? Also how could I specify different timestamps for the start and the end of the difference mentioned above (instead of the first and last ping). Here is an example of the table:

|--|---------|-------------------|----|
|id|http_code|pingtime           |host|
|--|---------|-------------------|----|
|10|200      |2014-09-16 09:08:05|2   |
|--|---------|-------------------|----|
|11|200      |2014-09-16 10:07:05|1   |
|--|---------|-------------------|----|
|12|200      |2014-09-16 10:14:10|2   |
|--|---------|-------------------|----|

I hope that I've explain myself clear enough.

look at here http://dev.mysql.com/doc/refman/5.6/en/date-calculations.html — Key_coder, Sep 16 '14 at 08:11

GuCier · Accepted Answer · 2015-01-29T15:53:19.770

11

You could use the native TIMESTAMPDIFF function :

SELECT TIMESTAMPDIFF(<INTERVAL>,<timestampFrom>,<timestampTo>);

If you want to find the difference between the first and the last timestamp of a given host ID, here you are:

SELECT TIMESTAMPDIFF(MINUTE,MIN(pingtime),MAX(pingtime))
FROM yourTable
WHERE host = 2;

edited Jan 29 '15 at 15:53

answered Sep 16 '14 at 08:05

GuCier

6,919
1
29
36

This is the best answer – Edward Sep 16 '14 at 08:09

score 2 · Answer 2 · edited Jan 05 '19 at 10:04

2

If you want to use in SQL syntax:

SELECT DATEDIFF(minute,'2008-11-30','2008-11-29') AS DiffDate

In addition to minute, you can use day as well.

edited Jan 05 '19 at 10:04

j413254

173
1
10

answered Sep 16 '14 at 08:08

mariosandoz

63
8

score 0 · Answer 3 · edited May 23 '17 at 11:49

The basic idea is to select the max and the min value for the pingtime of each host, and join the results of both subqueries.

I leave the time difference (SELECT TIMESTAMPDIFF(MINUTE, ..., ...)) and other details for you:

    SELECT FirstPing.host, FirstPing.first, LastPing.last
    FROM 
      (SELECT host, MAX(pingtime) AS last FROM Test GROUP BY host) AS LastPing
    INNER JOIN 
      (SELECT host, MIN(pingtime) AS first FROM Test GROUP BY host) AS FirstPing
    ON FirstPing.host = LastPing.host

As to expressing the first and the last times to consider for the ping times, simply add a where predicate in each subquery.

Edward · Answer 4 · 2014-09-16T08:09:31.963

0

You could convert it to UNIX TIMESTAMP and then use php's date() function like so:

$difference = date('Y-m-d H:i:s', strtotime($to) - strtotime($from));

This would give you the difference.

edited Sep 16 '14 at 08:09

answered Sep 16 '14 at 07:59

Edward

1,806
5
26
36

score 0 · Answer 5 · answered Sep 16 '14 at 08:12

0

SELECT

    TIMESTAMPDIFF(MINUTE,MAX(pingtime), MIN(pingtime))

FROM
    myTable

WHERE WHERE host = 2;

answered Sep 16 '14 at 08:12

Ankur Singhal

26,012
16
82
116

score 0 · Answer 6 · answered Dec 13 '21 at 14:30

0

I am using Azure SQL and for me the DATEDIFF function works fine when working with TIMESTAMPS, instead of TIMESTAMPDIFF

SELECT DATEDIFF(INTERVAL,timestampFrom,timestampTo)

You can use MINUTE, DAY or your choice of interval in INTERVAL

answered Dec 13 '21 at 14:30

tyler

91
8

Calculate the time difference between two timestamps in mysql

6 Answers6

Linked