Discrepancy in Total Device Distance Calculation between MySQL Query and Traccar Platform

Hello everyone,

I hope you're all doing well. I wanted to reach out and kindly request your assistance with a challenge we're facing. We are currently working on a MySQL query in Workbench to retrieve the total distance covered by each device for the previous month. The query we're using is as follows:

SELECT
    deviceid,
    SUM(JSON_EXTRACT(attributes, '$.distance')) as total_distance
FROM tc_positions
WHERE
    devicetime >= '2023-08-01 00:00:00' AND
    devicetime <= '2023-08-31 23:59:59'
GROUP BY deviceid;

The results we obtain from this query are as follows:

11    1592362.8800000031
28    3620268.3799999813
29    20923065.470000107
31    21057863.15999994
32    39122392.89000012
33    1257894.0400000005
35    1277968.7900000028
36    8919741.09000002

However, these results do not align with the distances obtained from the Traccar platform. We pulled the distances from the 'Last Month Summary' in Traccar, and the distances are displayed as follows:

aabb520    989.75 Km
aabb8839    17836.72 Km
ssbb2820    116360.18 Km
ssbb2398    1691.17 Km
vitara SZ    104289.86 Km
sss-181    1037.57 Km
dddd6328    1663.84 Km
scd232    1192.67 Km

It probably means that your devices are not reporting the data in the correct order.

But when I try to get it from the summary, the data is correct, any ideas if my mysq query has even logic? The result is off by a lot

If your device is not reporting the data in the right order, the numbers will be incorrect in Traccar.

The number's in traccar are correct, the number's in my query are the problem i think my query is wrong, any insides ?