Calculate the time difference between two timestamps in mysql
41,918
Solution 1
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;
Solution 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.
![MariusNV](https://i.stack.imgur.com/1ooOC.jpg?s=256&g=1)
Author by
MariusNV
Updated on December 14, 2021Comments
-
MariusNV over 2 years
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.