Drive Details 'Ø Speed' overstates average speed: point average is biased by standstill downsampling

[wjsall]

Summary

The Drive Details internal dashboard's Ø Speed stat (grafana/dashboards/internal/drive-details.json) computes average speed as a plain point average:

SELECT convert_km(avg(speed)::numeric, '$length_unit') AS speed_${length_unit}h
FROM positions
WHERE car_id = $car_id AND $__timeFilter(date)

Because streaming inserts positions much more densely while moving than while stopped, this systematically overstates average speed, especially in stop-and-go city driving.

Evidence (measured on real data)

A user provided a second-level export of one 24-minute city drive (7.51 km, panel showed 27 km/h):

• Sampling density: ~0.29 s/point while moving vs ~1.6 s/point at standstill (≈5.5× difference)
• Standstill was 30.6 % of wall time but only 7.5 % of samples
• Plain point average of all samples = 26.84 → displayed 27 km/h
• True average (distance ÷ duration) = 18.8 km/h → the panel overstated by +43 %

Reproduced independently on a second installation (different drive): point average 33.7 vs distance/duration 29.7 (+13 %). The bias grows with the share of stopped time, and numerically the point average happens to approach "distance ÷ moving time", which confuses users into thinking stopped time is intentionally excluded.

Suggested fix

Time-weighted average (equivalent to distance ÷ elapsed time within the window), e.g.:

WITH pts AS (
  SELECT speed,
         EXTRACT(EPOCH FROM (LEAD(date) OVER (ORDER BY date) - date)) AS dt
  FROM positions
  WHERE car_id = $car_id AND $__timeFilter(date) AND speed IS NOT NULL
)
SELECT convert_km((SUM(speed * dt) / NULLIF(SUM(dt), 0))::numeric, '$length_unit') AS speed_${length_unit}h
FROM pts
WHERE dt BETWEEN 0 AND 60  -- cap gaps so data holes don't skew the weighting

On the datasets above this returns 18.9 and 30.0 respectively, matching distance ÷ duration.

We ship a localized fork of the dashboards and have applied this fix in wjsall/teslamate-chinese-dashboards v1.8.1 (analysis in issue #31). Happy to open a PR here if the approach looks right to you.

[JakobLichterfeld]

Thanks for reporting. As a user I would expect neither. I would expect average speed and moving average

[JakobLichterfeld]

Thanks for your contribution!

As said in #5499

As a user I would expect neither. I would expect average speed and moving average.