计算mysql中两个时间戳之间的时差

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:

我在一个MySQL数据库表上工作,该表有一个包含时间戳(Ex,2014-09-16 09:08:05)的列,当时我ping了不同的主机。我的问题是如何在几分钟内计算出第一个ping和特定主机的最后一个ping之间的差异?另外,我如何为上面提到的差异的开始和结束指定不同的时间戳(而不是第一次和最后一次ping)。以下是该表的示例:

|--|---------|-------------------|----|
|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.

我希望我能够清楚地解释自己。

5 个解决方案

#1

You could use the native TIMESTAMPDIFF function :

您可以使用本机TIMESTAMPDIFF函数:

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:

如果要查找给定主机ID的第一个和最后一个时间戳之间的差异,请执行以下操作:

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

#2

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.

基本思想是为每个主机的pingtime选择最大值和最小值,并加入两个子查询的结果。

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

我给你留下时差(SELECT TIMESTAMPDIFF(MINUTE,...,...))和其他细节:

    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.

至于表达第一次和最后一次考虑ping时间,只需在每个子查询中添加where谓词。

#3

If you want to use in SQL syntax:

如果要在SQL语法中使用:

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

#4

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

您可以将其转换为UNIX TIMESTAMP,然后使用php的date()函数,如下所示:

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

This would give you the difference.

这会给你带来不同。

#5

SELECT

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

FROM
    myTable

WHERE WHERE host = 2;

#1