获取两列和几行之间的平均时间MYSQL [英] Get average time between two columns and several rows MYSQL
本文介绍了获取两列和几行之间的平均时间MYSQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两列,一列包含开始时间戳,另一列包含结束时间戳.我想计算所有记录的开始和结束之间的平均时间.目前我尝试了以下方法:
I have two columns one containing a start TIMESTAMP and one containing a END TIMESTAMP. I want to calculate the average time between start and end over all records. Currently I tried the following:
$result = $conn->query( $sql );
$averages = array();
$result = $conn->query( $sql ); # Place the result into an array
if ( $result->num_rows > 0 ) {
while ( $row = $result->fetch_assoc() ) {
$sum = 0;
if($EndColumn == ' '){
$time = $row[$StartColumn];
}else{
$time = strtotime($row[$EndColumn]) - strtotime($row[$StartColumn]);
$averages[] = $time;
}
}
$total = array_sum($averages);
$count = count($averages);
$avg = $total/$count;
return round(($total/$count));
这根本无法正常工作,我认为这可以在 sql 字符串本身中完成,但太愚蠢了无法弄清楚.无论哪种方式,请帮助!
This is not working correctly at all, I think this can be accomplished in the sql string itself but am too stupid to figure it out. Either way please help!
推荐答案
请试试这个:
SELECT CAST(AVG(start_date + (IFNULL(end_date,start_date)-start_date)/2) AS DATETIME) AS average_date FROM TableName
如果日期存储为时间戳,则上述查询将给出所有记录的开始和结束之间的平均时间.
If dates are stored as timestamp then above query will give average time between start and end over all records.
这篇关于获取两列和几行之间的平均时间MYSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文