我有一个gps php应用程序和一个包含路线旅行数据的数据库表:
START ROUTE(timeA) END ROUTE(timeB) DURATION LENGHT (KM)
'2018-08-11 00:09:33', '2018-08-11 01:06:36', '57:3 ', '53.29'
'2018-08-11 01:17:06', '2018-08-11 01:25:58', '8:52 ', '7.11'
'2018-08-11 01:49:24', '2018-08-11 01:49:44', '20 ', '0.05'
'2018-08-11 02:05:49', '2018-08-11 02:25:39', '19:50 ', '15.30'
'2018-08-11 02:35:20', '2018-08-11 03:54:24', '1:19:4 ', '84.62'
我需要计算日期间隔和小时间隔之间的每一天的总和。我对此提出了一个疑问:
$quu = ("SELECT SEC_TO_TIME(SUM(UNIX_TIMESTAMP(timeB) - UNIX_TIMESTAMP(timeA))) AS period, SUM(lenght) AS lenght FROM apm_travel_sheet WHERE timeA BETWEEN '$zistart' AND '$ziend' AND imei='$imei' ORDER BY timeA ASC");
$ress = mysql_query($quu) or die(mysql_error());
while($row = mysql_fetch_array($ress)) {
$period = $row['period'];
$lenght = round($row['lenght'], 2);
它工作得很好,但是如果路由在搜索间隔小时之前开始并以间隔结束,则查询不包括距离间隔分钟数。示例:我想对此间隔运行查询: start: 2018-08-01 22:00 end: 2018-08-02 06:00
如果在DB中,我有一个开始于2018-08-01 21:46,结束于2018-08-02 22:40的路由,结果它没有显示,因为开始时间不在间隔内。我需要修改查询以显示40分钟,因为是在选定的时间间隔,我没有任何想法…
感谢您的帮助!
https://stackoverflow.com/questions/51866704
复制相似问题