计算MySQL中两个时间戳之间的时间差 [英] Calculate the time difference between two timestamps in 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;

这篇关于计算MySQL中两个时间戳之间的时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！