3

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
  • 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? – Lennart Jul 26 '17 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 – Will KK Jul 26 '17 at 6:13
  • Not work is kind of vague, can you add expected result for your sample data? – Lennart Jul 26 '17 at 6:20
  • @Lennart updated ! – Will KK Jul 26 '17 at 6:30
  • To me, it looks like my predicate would return that, what is the difference? – Lennart Jul 26 '17 at 6:59
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.