Drive Stats 'mi driven' total is wrong

[youzer-name]

Is there an existing issue for this?

• [X] I have searched the existing issues

What happened?

I pulled stats for the past two days to review a road trip. I have two Teslas and this involves car #2 in case that matters. On the 'drives' screen it shows 8 drives with a total mileage of about 464 miles:

On the drive stats screen it shows 8 drives, but the 'mi driven' is only 271:

With 'Last 2 days' as the time setting, when I inspect the 'mi driven' query in Grafana I see this:

As you can see, 'Last 2 days' captures 6/12/2022 to 6/13/2022 in the drives dashboard, but 6/11/2022 to 6/12/2022 in the drive stats dashboard. (I am in EDT, UTC -0400, so it shows 6/10/2022 2000 hours and 6/11/2022 2000 hours)

To see if I could get it to count the part of the drive that happened after midnight local (on 6/13/2022) I put in an absolute date range of 2022-06-11 00:00:00 to 2022-06-15 23:59:59 EDT, but that still results in the wrong mileage:

The Query for the absolute date range shows as:

''' WITH since as ( SELECT date FROM positions WHERE car_id = '2' ORDER BY date ASC LIMIT 1 ), actual AS ( SELECT date_trunc('day', date)::date AS date, max(odometer) - min(odometer) AS distance FROM positions WHERE car_id = '2' GROUP BY 1 ), base_line AS ( SELECT date_trunc('day', dd)::date AS date FROM generate_series((select date from since) , now(), '1 day'::interval) dd ) SELECT base_line.date AS "time", convert_km(COALESCE(distance, 0)::numeric, 'mi') as "distance_mi" FROM base_line LEFT JOIN actual ON actual.date = base_line.date WHERE base_line.date BETWEEN '2022-06-11T04:00:00Z' AND '2022-06-16T03:59:59Z' ORDER BY 1; '''

I don't understand the query well enough to figure out where the discrepancy is happening.

Expected Behavior

I expected the Drive Stats -> mi driven panel to show 464 miles.

Steps To Reproduce

I'm not sure. Is it because one of the drives spans two different dates (started before midnight, ended after) in local time, or is something else wrong with the drive stats -> mi driven query?

Relevant log output

No relevant logs.

Screenshots

No response

Additional data

drives.csv positions.csv

Type of installation

Docker

Version

v1.26.1

[DrMichael]

Try the following in "mi driven":

SELECT
	start_date AS time,
	convert_km(sum(end_km - start_km)::numeric, '$length_unit') AS distance
FROM drives
WHERE car_id = '1'AND
  $__timeFilter(start_date)
GROUP BY 1;

[youzer-name]

The query you sent me seems to work. I had to create a Stat panel in a new dashboard, since the Teslamate dashboards aren't editable, so it took me a couple tries to figure out how to make it work. Once I removed the $length_unit and switched it to car_id='2' then I got the correct total.

So with my date range set to

[image: image.png]

The query was:

SELECT start_date AS time, convert_km(sum(end_km - start_km)::numeric, 'mi') AS distance FROM drives WHERE car_id = '2'AND start_date BETWEEN '2022-06-11T04:00:00Z' AND '2022-06-13T07:59:59Z' GROUP BY 1;

And the total is correct.

I also tried changing my date range to be just one day:

[image: image.png]

And this also produced the correct total (463 miles) which included a drive that started on 2022-06-12 and ended on 2022-06-13, but excluded a 1-mile drive that started on 2022-06-13.

Is there an easy way to update the Teslamate dashboard to use the new query, or should I just wait for an update?

Thanks for your help,

Alan

---

Everything below is more information about how the original query is working. If there is no need to further analyze the original query, you can ignore everything below.

Now when I run the original query from my issue report (it was about 6/15/2022 @ 0830 EDT when I ran it) the total distance is higher than before, but still wrong. When I set an absolute time range from 2022-06-11 00:00:00 to 2022-06-13 03:59:59 EDT, I get the correct mileage for 6/11 and 6/12, but it also includes 41.1 miles from 2022-06-10 20:00:00.

[image: image.png]

[image: image.png]

Query inspector:

WITH since as ( SELECT date FROM positions WHERE car_id = '2' ORDER BY date ASC LIMIT 1 ), actual AS ( SELECT date_trunc('day', date)::date AS date, max(odometer) - min(odometer) AS distance FROM positions WHERE car_id = '2' GROUP BY 1 ), base_line AS ( SELECT date_trunc('day', dd)::date AS date FROM generate_series((select date from since) , now(), '1 day'::interval) dd ) SELECT base_line.date AS "time", convert_km(COALESCE(distance, 0)::numeric, 'mi') as "distance_mi" FROM base_line LEFT JOIN actual ON actual.date = base_line.date WHERE base_line.date BETWEEN '2022-06-11T04:00:00Z' AND '2022-06-13T11:59:59Z' ORDER BY 1;

So even though the query clearly says 2022-06-11 04:00:00Z as the start time, the mi driven panel is including drives that occurred on 2022-06-10.

On Wed, Jun 15, 2022 at 6:43 AM DrMichael @.***> wrote:

Try the following in "mi driven":

SELECT start_date AS time, convert_km(sum(end_km - start_km)::numeric, '$length_unit') AS distance FROM drives WHERE car_id = '1'AND $__timeFilter(start_date) GROUP BY 1;

— Reply to this email directly, view it on GitHub https://github.com/adriankumpf/teslamate/issues/2701#issuecomment-1156311220, or unsubscribe https://github.com/notifications/unsubscribe-auth/ANTHLHKN5UP6L2BP4BDRFP3VPGXV7ANCNFSM5YVCZA5A . You are receiving this because you authored the thread.Message ID: @.***>

[DrMichael]

@adriankumpf @cwanja Do you know, why we have such a complicated query in the Drive Stats? If not, I would push that here...

[cwanja]

@adriankumpf @cwanja Do you know, why we have such a complicated query in the Drive Stats? If not, I would push that here...

Unknown to me. If there is a simpler method, I would definitely go that route. I feel like I have seen other threads where the count in drive stats, versus trips, versus drives are all different variables.

[youzer-name]

I think I see issues with the extrapolated monthly mileage and annual mileage on this dashboard also.

It appears that it is using only the days where the car was driven, not the total number of days in the time range, to determine the extrapolated mileage. For example if I select "Last 7 days" I get 607 miles total. The extrapolated monthly mileage is 3,036 and annual is 36.940.

The average daily mileage is: 607/7 ~= 87

In a 30-day month that would be 2,610 miles per month. In a 365-day year that would be: 87 * 365 = 31,755

I think this is because there was one day in the past 7 that the car was not driven. If I divide the mileage by 6 instead of 7 to get the daily mean, then I get numbers closer to what the dashboard shows.

Using only 6 days, 607/6 = 101.166. In a 30 day month that would be 101.166 * 365 = 3,035 miles per month (within 1 mi of the dashboard result). In a 52-week year that would be 101.166 * 365 = 36,925 (within 15 miles of the dashboard result).

This only gets worse if you have fewer days driven. I just tried from 2022-06-12 to 2022-06-18 (which includes future dates), resulting in 470 miles driven in that 7 day span. There are only drives for 2 of the 7 days in that span. (Actually there is a 3rd day, but the mileage is <1 mile so I think it gets filtered out). The extrapolated annual mileage is 85,834, which is what you would get using 470 / 2 * 365, but using the average of all 7 days it should be 470 / 7 = 67.14 | 67.14 * 365 = 24,506.

Shouldn't the extrapolated mileage include zero-mile days? If I drive 100 miles once a week, then my extrapolated annual mileage is 5,200 miles, not 36,500.

edit: I just refreshed the dashboard and now it seems to be including the date with the sub-1-mile drive, so extrapolated annual mileage is 57,233 - which is still way too high based on the 7-day average.

[DrMichael]

I think, I had that discussion earlier. It is the median, not the average. :-)

[youzer-name]

@DrMichael I don't think it is using the median. For the last scenario I described (470 miles, 7-day span, 3 days driven) my median distance per day is 230 miles. (per drive is only 3.02... it definitely isn't using that).

Monthly: 230 / 7 = 32.86 | 32.86 * 30 = 986 miles per month (dashboard shows 4,703) 230 / 3 = 76.66 | 76.66 * 30 = 2,300 miles per month (dashboard shows 4,703)

Annual: 230 / 7 = 32.86 | 32.86 * 365 = 11,994 per year (dashboard shows 57,223) 230 / 3 = 76.66 | 76.66 * 365 = 27,981 per year (dashboard shows 57,233)

The only way to get the result the dashboard is showing is

Total mileage / (number of days with a drive) * (30 or 365)

It should be:

Total mileage / (number if days in the range) * (30 or 365)

[DrMichael]

Perhaps Drive Stats is more for the long term observation, Trip might be better for short term...

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 7 days if no further activity occurs. Thank you for your contributions.