计算MySQL中两个时间戳之间的时间差
我在一个MySQL数据库表上工作,该表的列包含我对其他主机执行ping操作时的时间戳记(例如2014-09-16 09:08:05).我的问题是如何在几分钟内计算出针对特定主机的第一次ping和最后一次ping之间的差异?另外,如何为上述差异的开始和结束指定不同的时间戳(而不是第一次和最后一次ping).这是表格的示例:
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.
您可以使用本机如果要查找给定主机ID的第一个时间戳和最后一个时间戳之间的时差,请按以下步骤操作:
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;