两个日期时间之间的php mysql查询，但如果在范围内完成，则包含距离范围外的分钟数

2g32fytz 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(306)

我有一个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);

它工作得很好，但是如果路由在搜索间隔小时之前开始并在间隔中结束，则查询不包括间隔的分钟数。示例：我要为此间隔运行查询：开始：2018-08-01 22:00结束：2018-08-02 06:00
如果在db中，我有一条路线在2018-08-01 21:46开始，在2018-08-02 22:40结束，结果它没有显示，因为开始时间不在间隔内。我需要修改查询，以显示40分钟，因为是在选定的时间间隔，我不知道。。。
谢谢你的帮助！

mysql php

来源：https://stackoverflow.com/questions/51866704/php-mysql-query-between-two-date-time-but-include-minutes-from-outside-range-if

2条答案

按热度按时间

rkkpypqq1#

我找到了满足我需要的答案。我会贴在下面也许会帮助其他人。很好用。

$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";

赞(0）回复(0）举报 2021-06-20

aelbi1ox2#

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 |
+----+---------------------+---------------------+-------------+

赞(0）回复(0）举报 2021-06-20

我来回答

两个日期时间之间的php mysql查询，但如果在范围内完成，则包含距离范围外的分钟数

2条答案

相关问题

热门标签

最新问答