# How to calculate total distance by datetime

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

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
``````
• Not sure what you mean, is it: `where p_time >= '2017-06-23 12:00:00' and c_time <= '2017-06-23 14:00:00`? Commented Jul 26, 2017 at 6:01
• mean calculate from `2017-06-23 12:00:00` to `2017-06-23 14:00:00`.. i tried the query before, but not work Commented Jul 26, 2017 at 6:13
• Not work is kind of vague, can you add expected result for your sample data? Commented Jul 26, 2017 at 6:20
• @Lennart updated ! Commented Jul 26, 2017 at 6:30
• To me, it looks like my predicate would return that, what is the difference? Commented Jul 26, 2017 at 6:59

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