MySQL 计算mysql中两个时间戳的时间差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25863144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate the time difference between two timestamps in mysql
提问by MariusNV
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 数据库表上工作，该表有一列包含我 ping 不同主机时的时间戳（例如 2014-09-16 09:08:05）。我的问题是如何在几分钟内计算特定主机的第一个 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.
我希望我已经足够清楚地解释了自己。
回答by Creaforge
You could use the native TIMESTAMPDIFFfunction :
您可以使用本机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;
回答by mariosandoz
If you want to use in SQL syntax:
如果要在 SQL 语法中使用：
SELECT DATEDIFF(minute,'2008-11-30','2008-11-29') AS DiffDate
In addition to minute, you can use day as well.
除了分钟，您还可以使用天。
回答by Ankur Singhal
SELECT
TIMESTAMPDIFF(MINUTE,MAX(pingtime), MIN(pingtime))
FROM
myTable
WHERE WHERE host = 2;
回答by JotaBe
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 wherepredicate in each subquery.
至于表示ping次数要考虑的第一次和最后一次，只需where在每个子查询中添加一个谓词即可。
回答by Edward
You could convert it to UNIX TIMESTAMP and then use php's date()function like so:
您可以将其转换为 UNIX TIMESTAMP，然后date()像这样使用 php 的函数：
$difference = date('Y-m-d H:i:s', strtotime($to) - strtotime($from));
This would give you the difference.
这会给你带来不同。
