Mineng Query - Reservations Between a certain time Period

use socar_malaysia;
select
    r.id, r.member_id, m.first_name, m.last_name, r.way, r.state, r.start_zone_id as zone_id, z.city as city, z.region as region, z.name as zone_name, r.car_id, c.registration_no, cs.car_name as cartype,
    convert_tz(r.created_at, '+00:00', '+8:00') as rc, convert_tz(r.start_at, '+00:00', '+8:00') as rs, convert_tz(r.end_at, '+00:00', '+8:00') as re,
    (timestampdiff(minute, r.start_at, r.end_at)/60) as dur,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='rent'),0) as rent,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','d2d','mileage')),0)
    - ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0) as nr

from reservations r, zones z, members m, car_classes cs, cars c
where
    r.start_zone_id = z.id
    and r.member_id = m.id
    and c.id = r.car_id
    and c.car_class_id = cs.id
    and m.imaginary in ('sofam', 'normal')
    and m.state in ('normal')
    and z.state in ('normal')
    and r.state in ('inuse','completed')

    #return cases
    and convert_tz(r.return_at, '+00:00', '+8:00') between '2019-08-22 20:00:00' and '2019-08-23 01:00:00'

    #reservation start cases
    #and convert_tz(r.start_at, '+00:00', '+8:00') between '2019-08-22 20:00:00' and '2019-08-23 01:00:00'

    #inuse case
    #and convert_tz(r.start_at, '+00:00', '+8:00') < '2019-08-22 20:00:00'
    #and convert_tz(r.end_at, '+00:00', '+8:00') > '2019-08-23 01:00:00'

group by r.id
order by re desc
;