计算MySQL中插入的第一行和最后一行之间经过的时间

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

---

想要改进这个问题？通过编辑这篇文章添加详细信息并澄清问题。

7 年前已关闭。

Answer 1

SELECT TIMEDIFF( MAX(time_col), MIN(time_col) ) FROM [tablename];

当它按时间顺序存储时，这将很有帮助。但是时间戳列可以通过更新来修改，所以我建议使用下面的查询而不是上面的查询。

SELECT TIMEDIFF( x.time_col, y.timecol ) FROM 
    ( SELECT time_col FROM [tablename] ORDER BY [primary_key] DESC LIMIT 1 ) x,
    ( SELECT time_col FROM [tablename] ORDER BY [primary_key] ASC  LIMIT 1 ) y

---

您也可以使用 TIMESTAMPDIFF 函数。

SELECT TIMESTAMPDIFF( SECOND, x.time_col, y.timecol ) FROM 
    ( SELECT time_col FROM [tablename] ORDER BY [primary_key] DESC LIMIT 1 ) x,
    ( SELECT time_col FROM [tablename] ORDER BY [primary_key] ASC  LIMIT 1 ) y

Answer 2

这个例子是在 php 中使用 mysql_* 函数，
您可以替换为您喜欢的任何方法

$con = mysql_connect(...); // your connection
$sql = <<<SQL
select 
  max(the_field) as last_time ,
  min(the_field) as first_time,
  timestampdiff(second, min(the_field), max(the_field)) as diff_time
from your_table
SQL;
$res = mysql_query($sql, $con);
$row = mysql_fetch_assoc($res);
mysql_free_result($res);