Mysql – How to calculate total distance by datetime

datetimeMySQL

This is my database table ↓↓

ID   GPSLat     GPSLon       GPSTime
(1 , 3.214502 , 101.638419, '2017-06-23 12:21:51'),
(2 , 3.214478 , 101.638101, '2017-06-23 12:20:42'),
(3 , 3.214668 , 101.638049, '2017-06-23 13:25:53'),
(4 , 3.215089 , 101.638022, '2017-06-23 13:19:12'),
(5 , 3.215196 , 101.638626, '2017-06-23 14:10:56'),
(6 , 3.215255 , 101.639087, '2017-06-24 15:10:48'),
(7 , 3.215011 , 101.639448, '2017-06-24 15:10:36'),
(8 , 3.215041 , 101.640151, '2017-06-24 16:10:26'),
(9 , 3.218021 , 101.620416, '2017-06-24 17:31:59'),
(10, 3.123456 , 101.555555, '2017-06-25 18:45:41'),
(11, 3.345214 , 101.632157, '2017-06-25 18:02:51');

the distances result in create view image ↓↓

enter image description here

I'm using the following query to sum up the distances by prev_ to curr_, but how do I calculate the distances by date or time instead of using prev_ and curr_?

For example, I want to calculate from date "2017-06-23 12:00:00" to "2017-06-23 14:00:00", which is output came from id1 to id4, how do I calculate and using the following similar query?

PS:I tried LIKE operator but it's looks tricky

select 
 sum(distance_km) total_km
from 
  distances
where prev_ >=2 and curr_ <=11;

this is my formula to calculate between each id distance ↓↓ (I'm not sure the code it's whether affect the datetime , so I post it out too)

create view distances as
select
  prev.id prev_
, curr.id curr_
, prev.gpslat p_lat_
, prev.gpslon p_lon_
, curr.gpslat c_lat_
, curr.gpslon c_lon_
, prev.gpstime p_time_
, curr.gpstime c_time_
, ROUND( 6353 * 2 * 
    ASIN(SQRT( POWER(SIN((curr.GPSLat - abs(prev.GPSLat)) * pi()/180 / 2),2) 
  + COS(curr.GPSLat * pi()/180 ) * COS( abs(prev.GPSLat) *  pi()/180) 
  * POWER(SIN((curr.GPSLon - prev.GPSLon) * pi()/180 / 2), 2) )), 2) as distance_km
from gpsdata prev
  join gpsdata curr 
    on prev.id = curr.id - 1
where curr.id >= 1;

Edited: Expected Result

select datetime from 2017-06-23 12:00:00 to 2017-06-23 14:00:00

prev_ | curr_ | p_time_             | c_time_             | distance_km
1     | 2     | 2017-06-23 12:21:51 | 2017-06-23 12:20:42 | 0.04
2     | 3     | 2017-06-23 12:20:42 | 2017-06-23 13:25:53 | 0.02
3     | 4     | 2017-06-23 13:25:53 | 2017-06-23 13:19:12 | 0.05

total_km
0.11

Best Answer

I have tried by filtering your view by dates and IMHO it works fine:

select   select   prev_, curr_, p_time_, c_time_, distance_km
from     distances
where    p_time_ >= '2017-06-23 12:00:00' 
and      c_time_ <= '2017-06-23 14:00:00'
order by prev_
;

| prev_ | curr_ | p_time_             | c_time_             | distance_km |
|-------|-------|---------------------|---------------------|-------------|
| 1     | 2     | 23.06.2017 12:21:51 | 23.06.2017 12:20:42 | 0,04        |
| 2     | 3     | 23.06.2017 12:20:42 | 23.06.2017 13:25:53 | 0,02        |
| 3     | 4     | 23.06.2017 13:25:53 | 23.06.2017 13:19:12 | 0,05        |

select sum(distance_km) as total_km
from   distances
where  p_time_ >= '2017-06-23 12:00:00' 
and    c_time_ <= '2017-06-23 14:00:00'
;

| total_km |
|----------|
| 0,11     |

Rextester here

Related Question