1

I have a gps php application and a database table with route travel data: 

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'

I need to calculate sum of each days between an date interval and hours interval. I have made a query for that:

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

It work great, but if the route start before search interval hours and end in interval, query do not include minutes from interval. Example: I want to run query for this interval: start: 2018-08-01 22:00 end: 2018-08-02 06:00

If in DB I have a route that start at 2018-08-01 21:46 and finish at 2018-08-02 22:40 in result it not shown because start is not in interval. I need to modify query to show that 40 minutes because is in selected interval and I do not have any idea...

Thank you for help!

Danon
  • 2,771
  • 27
  • 37
Eugen Dumitrescu
  • 21
  • 3
  • Note that this insecure API was deprecated a very, very long time ago. Presumably, this is just for archaeological interest? – Strawberry Aug 15 '18 at 22:04
  • 1
    [Why you should not use mysql_* functions in PHP](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – divibisan Aug 15 '18 at 23:11

2 Answers2

1
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 |
+----+---------------------+---------------------+-------------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57
1

I find the answer for my needs. I will post below maybe will help another people. It work great.

$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";
Eugen Dumitrescu
  • 21
  • 3