>我正在尝试向 mysql 查询多个记录的平均 SUM 时间（取自日期时间字段），以获得如下输出： 22：38
我的日期时间字段checkin_time包含以下数据：
2012-03-16 22:48:00 // the time here: 22:48 is what's interesting to me
2012-03-16 02:28:32
2012-03-16 00:28:47
0000-00-00 00:00:00
我的计划是从所有日期时间字段中提取并选择总时间，然后将总和转换为 unix 时间戳，将总和除以记录总数，最后将其转换回时间格式。然而，这（见下面的代码）什么也没给我，没有错误，什么都没有。我还意识到，在生成相关数据时，不会考虑诸如 0000-00-00 00：00：00 之类的空字段。
谁能帮我指出错误，或者解释一下你如何做到这一点背后的理论？这是我到目前为止得到的：
编辑：感谢Damiqib建议一个有效的SQL查询，但仍然不完全正确。下面的代码输出 01：00，而它应该是 23：15 左右。
$getCheckinTime = mysql_query("SELECT COUNT(id), SEC_TO_TIME( AVG( TIME_TO_SEC( `checkin_time` ) ) ) AS averageTime FROM guests WHERE checkin_time != '0000-00-00 00:00:00'") or die(mysql_error());
while($checkIn = mysql_fetch_array($getCheckinTime)) {
$timestamp = strtotime($checkIn['averageTime']);
$UnixTimeStamp = date("Y-m-d H:i:s", $timestamp); //converting to unix
$avgUnix = $UnixTimeStamp / $checkIn['COUNT(id)']; // calculating average
$avgTime = date('H:i', $avgUnix); // convert back to time his format
echo $avgTime; //outputs 01:00, got to be incorrect should be 23:15 something
}
提前致谢
编辑：解决方案（感谢达米奇布）：
$avgCheckinTime = array();
$getCheckinTime = mysql_query("SELECT TIME(`checkin_time`) AS averageTime FROM guests WHERE checkin_time != '0000-00-00 00:00:00'") or die(mysql_error());
while($checkIn = mysql_fetch_array($getCheckinTime)) {
array_push($avgCheckinTime, $checkIn['averageTime']);
}
// = array('22:00:00', '22:30:00'...)
$times = $avgCheckinTime;
$fromReplace = array('22' => '00',
'23' => '01',
'00' => '02',
'01' => '03',
'02' => '04',
'03' => '05',
'04' => '06',
'05' => '07');
$timeSum = 0;
//Iterate through all given times and convert them to seconds
foreach ($times as $time) {
if (preg_match ('#^(?<hours>['d]{2}):(?<mins>['d]{2}):(?<secs>['d]{2})$#',$time, $parse)) {
$timeSum += (int) $fromReplace[$parse['hours']] * 3600 + (int) $parse['mins'] * 60 + (int) $parse['secs'] . '<br />';
//echo $time . ' ' . ($fromReplace[$parse['hours']] *3600) . '<br />';
}
}
$toReplace = array('00' => '22',
'01' => '23',
'02' => '00',
'03' => '01',
'04' => '02',
'05' => '03',
'06' => '04',
'07' => '05');
$time = explode(':', gmdate("H:i:s", $timeSum / count($times)));
$averageCheckinTime = $toReplace[$time[0]] . ':' . $time[1] . ':' . $time[2];
//This is the final average time biased between 22-05
echo $averageCheckinTime;
这似乎适用于我的测试数据：
SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(`time`))) AS averageTime
FROM guests
WHERE checkin_time != '0000-00-00 00:00:00'
更新：
-- Table structure for table `guests`
CREATE TABLE `guests` (
`checkin_time` datetime NOT NULL,
KEY `checkin_time` (`checkin_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- Dumping data for table `guests`
INSERT INTO `guests` VALUES('2012-08-17 17:30:00');
INSERT INTO `guests` VALUES('2012-08-17 18:30:00');
INSERT INTO `guests` VALUES('2012-08-17 19:30:00');
INSERT INTO `guests` VALUES('2012-08-17 20:30:00');
INSERT INTO `guests` VALUES('2012-08-17 21:30:00');
Showing rows 0 - 0 (1 total, Query took 0.0003 sec)
SELECT SEC_TO_TIME( AVG( TIME_TO_SEC( `checkin_time` ) ) ) AS averageTime
FROM guests
WHERE checkin_time != '0000-00-00 00:00:00'
LIMIT 0 , 30
结果
averageTime
19:30:00
至少在我的测试数据中，这似乎有效吗？
另一个更新
<?php
/*
SELECT TIME(`checkin_time`) AS averageTime
FROM guests
WHERE checkin_time != '0000-00-00 00:00:00'
*/
// = array('22:00:00', '22:30:00'...)
$times = RESULT_FROM_QUERY_AS_AN_ARRAY_OF_TIMES;
$fromReplace = array('22' => '00',
'23' => '01',
'00' => '02',
'01' => '03',
'02' => '04',
'03' => '05',
'04' => '06',
'05' => '07');
$timeSum = 0;
//Iterate through all given times and convert them to seconds
foreach ($times as $time) {
if (preg_match ('#^(?<hours>['d]{2}):(?<mins>['d]{2}):(?<secs>['d]{2})$#',$time, $parse)) {
$timeSum += (int) $fromReplace[$parse['hours']] * 3600 + (int) $parse['mins'] * 60 + (int) $parse['secs'] . '<br />';
echo $time . ' ' . ($fromReplace[$parse['hours']] *3600) . '<br />';
}
}
$toReplace = array('00' => '22',
'01' => '23',
'02' => '00',
'03' => '01',
'04' => '02',
'05' => '03',
'06' => '04',
'07' => '05');
$time = explode(':', gmdate("H:i:s", $timeSum / count($times)));
$averageCheckinTime = $toReplace[$time[0]] . ':' . $time[1] . ':' . $time[2];
//This is the final average time biased between 22-05
echo $averageCheckinTime;
?>