php MySQL查询介于两个日期时间之间，但如果在范围内完成，则包括超出范围的分钟数
php MySQL查询介于两个日期时间之间，但如果在范围内完成，则包括超出范围的分钟数
提问于 2018-08-16 05:28:36
我有一个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分钟，因为是在选定的时间间隔，我没有任何想法…
感谢您的帮助！
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-08-26 04:08:27
我找到了满足我需求的答案。我会在下面发帖，也许会对其他人有所帮助。它工作得很好。
$quu ="SELECT
tableCalculated.lenght,
tableCalculated.timeACalculated AS timeA,
tableCalculated.timeBCalculated AS timeB,
TIMEDIFF(tableCalculated.timeBCalculated, tableCalculated.timeACalculated) AS period
FROM
(
SELECT
lenght,
timeA,
(CASE
WHEN TIMEDIFF(timeA, '$zistart') < 0 THEN '$zistart'
WHEN TIMEDIFF(timeA, '$zistart') > 0 THEN timeA END) AS timeACalculated,
timeB,
(CASE
WHEN TIMEDIFF(timeB,'$ziend') < 0 THEN timeB
WHEN TIMEDIFF(timeB,'$ziend') > 0 THEN '$ziend' END) AS timeBCalculated
FROM
`apm_travel_sheet`
WHERE
imei = '$imei'
HAVING
timeBCalculated <= '$ziend' AND timeACalculated >= '$zistart' ORDER BY id ASC
) AS tableCalculated
HAVING period > 0";Stack Overflow用户
发布于 2018-08-16 06:10:11
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,start_dt DATETIME
,end_dt DATETIME
,distance_km DECIMAL(6,2)
);
INSERT INTO my_table VALUES
(1, '2018-08-11 00:09:33', '2018-08-11 01:06:36', 53.29),
(2, '2018-08-11 01:17:06', '2018-08-11 01:25:58', 7.11),
(3, '2018-08-11 01:49:24', '2018-08-11 01:49:44', 0.05),
(4, '2018-08-11 02:05:49', '2018-08-11 02:25:39', 15.30),
(5, '2018-08-11 02:35:20', '2018-08-11 03:54:24', 84.62),
(6, '2018-08-01 21:46:00', '2018-08-02 22:40:00', 50.05);
SELECT * FROM my_table WHERE end_dt >= '2018-08-01 22:00' AND start_dt <= '2018-08-02 06:00';
+----+---------------------+---------------------+-------------+
| id | start_dt | end_dt | distance_km |
+----+---------------------+---------------------+-------------+
| 6 | 2018-08-01 21:46:00 | 2018-08-02 22:40:00 | 50.05 |
+----+---------------------+---------------------+-------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51866704
复制相关文章
相似问题