Efficiency by speed

[jirisykora83]

It would be nice to have efficiency page by speed basically same as current efficiency tab which is by outside temperature.

[jheredianet]

Hi, It's a good idea. I'll give a try in the following days.
Stay tuned!

[jirisykora83]

I think about it more and if it should be perfect then it would be group by temperature / elevation but i guess it will be to complex then (i am not grafana expert).

Something like:

130-135 -------- AVG xx.xx kwh
10-15C --------- AVG xx.xx kwh
15-20C --------- AVG xx.xx kwh
...
...
140-140 -------- AVG xx.xx kwh
10-15C --------- AVG xx.xx kwh
15-20C --------- AVG xx.xx kwh

And about elevation i am not sure.

[jheredianet]

Hi, I've been working on it. Please could you check it out at the "develop" branch?

https://github.com/jheredianet/Teslamate-CustomGrafanaDashboards/blob/develop/dashboards/SpeedRates.json

Just import it manually and tell me how it looks in your environment.
Actually, I just considered the efficiency by speed and a graph of top speeds, this is how it looks:

Let me know, what you think.

[jirisykora83]

Hi, i tried it and it looks great. But i am little confused by Efficiency is over 100% for several speeds what exactly does that means? If so it is cool.

From quick inspection of query i cannot understand how you are able to get avg consumption for 115 for example. Is it like when whole "drive" has AVG speed 115km/h or it is it able to extract parts when it is 115km/h and parts when it is for example 80km/h?

[jheredianet]

Hi, i tried it and it looks great. But i am little confused by Efficiency is over 100% for several speeds what exactly does that means? If so it is cool.

Yes, it is because it's taking the current rated efficiency of the car, so it means that at those speeds where you get over 100%, your car is more efficient than its rated efficiency.

From quick inspection of query i cannot understand how you are able to get avg consumption for 115 for example. Is it like when whole "drive" has AVG speed 115km/h or it is it able to extract parts when it is 115km/h and parts when it is for example 80km/h?

Yes, What I do is to get an average speed of all drives (form the time picker) which dependes on distante and time. Then, they are grouped by steps of 5km/h or 5mi/h to get the complete list (Efficiency, Consumption), then you can see the average, max and mean distance traveled on that speed.

[jirisykora83]

I think for more precise values it has to use positions table. I am not an expert but i try to play with chat gpt.

WITH DifferentialData AS (
    SELECT
        positions.drive_id,
        positions.speed,
        positions.power,
        positions.date,
        positions.elevation - LAG(positions.elevation, 1) OVER (PARTITION BY positions.drive_id ORDER BY positions.date) AS elevation_diff
    FROM
        drives
    JOIN positions ON drives.id = positions.drive_id
    WHERE
        drives.start_date >= '2023-01-01' AND drives.end_date <= '2023-12-31'
)

, BinnedData AS (
    SELECT
        CASE
            WHEN elevation_diff > 0.5 THEN 'Uphill' 
            WHEN elevation_diff < -0.5 THEN 'Downhill'
            ELSE 'Flat'
        END AS terrain_type,
        width_bucket(speed, ARRAY[0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70, 75, 80, 85, 90, 95, 100, 105, 110, 115, 120, 125, 130, 135, 140, 145, 150, 155, 160, 165, 170, 175, 180, 185, 190, 195, 200]) AS speed_bin,
        SUM(power * (speed/60.0)) AS total_energy_consumed,
        SUM(speed/60.0) AS total_distance,
        AVG(speed) AS avg_speed,
        AVG(power) AS avg_power,
        COUNT(*) AS count_data
    FROM
        DifferentialData
    GROUP BY
        terrain_type, speed_bin
)

SELECT
    terrain_type,
    CASE 
                WHEN speed_bin = 1 THEN '0-5 km/h'
        WHEN speed_bin = 2 THEN '5-10 km/h'
        WHEN speed_bin = 3 THEN '10-15 km/h'
        WHEN speed_bin = 4 THEN '15-20 km/h'
        WHEN speed_bin = 5 THEN '20-25 km/h'
        WHEN speed_bin = 6 THEN '25-30 km/h'
        WHEN speed_bin = 7 THEN '30-35 km/h'
        WHEN speed_bin = 8 THEN '35-40 km/h'
        WHEN speed_bin = 9 THEN '40-45 km/h'
        WHEN speed_bin = 10 THEN '45-50 km/h'
        WHEN speed_bin = 11 THEN '50-55 km/h'
        WHEN speed_bin = 12 THEN '55-60 km/h'
        WHEN speed_bin = 13 THEN '60-65 km/h'
        WHEN speed_bin = 14 THEN '65-70 km/h'
        WHEN speed_bin = 15 THEN '70-75 km/h'
        WHEN speed_bin = 16 THEN '75-80 km/h'
        WHEN speed_bin = 17 THEN '80-85 km/h'
        WHEN speed_bin = 18 THEN '85-90 km/h'
        WHEN speed_bin = 19 THEN '90-95 km/h'
        WHEN speed_bin = 20 THEN '95-100 km/h'
        WHEN speed_bin = 21 THEN '100-105 km/h'
        WHEN speed_bin = 22 THEN '105-110 km/h'
        WHEN speed_bin = 23 THEN '110-115 km/h'
        WHEN speed_bin = 24 THEN '115-120 km/h'
        WHEN speed_bin = 25 THEN '120-125 km/h'
        WHEN speed_bin = 26 THEN '125-130 km/h'
        WHEN speed_bin = 27 THEN '130-135 km/h'
        WHEN speed_bin = 28 THEN '135-140 km/h'
        WHEN speed_bin = 29 THEN '140-145 km/h'
        WHEN speed_bin = 30 THEN '145-150 km/h'
        WHEN speed_bin = 31 THEN '150-155 km/h'
        WHEN speed_bin = 32 THEN '155-160 km/h'
        WHEN speed_bin = 33 THEN '160-165 km/h'
        WHEN speed_bin = 34 THEN '165-170 km/h'
        WHEN speed_bin = 35 THEN '170-175 km/h'
        WHEN speed_bin = 36 THEN '175-180 km/h'
        WHEN speed_bin = 37 THEN '180-185 km/h'
        WHEN speed_bin = 38 THEN '185-190 km/h'
        WHEN speed_bin = 39 THEN '190-195 km/h'
        WHEN speed_bin = 40 THEN '195-200 km/h'
        ELSE 'unknown'
    END AS speed_range,
    CASE 
        WHEN total_distance > 0 THEN (total_energy_consumed / total_distance) * 100
        ELSE NULL
    END AS avg_consumption_wh_per_100km,
    avg_speed,
    avg_power,
    count_data
FROM
    BinnedData
ORDER BY
    terrain_type, speed_bin;

Can you check it?

Also, i am thinking about splitting it by outside temp / (AC on / Heater ON - could be tricky) / battery heater (or should we "exclude battery heater power). But in the end, it may be good to have more tables split by different think or be able to somehow filter it.

[jirisykora83]

[jirisykora83]

consumption seems off i guess I (chatGPT) make some mistake also this do not include efficiency against rated range should be possible to add

[jheredianet]

I see, but finally you are going to have the same as I did before. Drives table is a resume of positions, so it's not necessary to query positions again except you need some data it is not considered on drives.
This is the raw query I did. Based on that could you explain what would you like to do? (Maybe I'm missing something or don't understand what you need)

WITH t AS (
	SELECT
		sum(start_ideal_range_km - end_ideal_range_km) AS total_ideal_range,
		sum(start_rated_range_km - end_rated_range_km) AS total_rated_range,
		sum(distance) AS total_distance,
		sum(duration_min) as duration,
        CASE WHEN 'km' = 'km' THEN 1 WHEN 'km' = 'mi' THEN 1.60934 END AS distance_factor,
		car_id
	FROM
		drives 
	WHERE
		distance IS NOT NULL
		AND drives.car_id = 1
		AND convert_km(distance::numeric, 'km') >= 1 
		AND start_rated_range_km - end_rated_range_km > 0.1
	GROUP BY id
),  data AS (
    SELECT
        total_distance / total_rated_range AS efficiency,
    	total_rated_range / total_distance * c.efficiency * 1000 * distance_factor AS consumption_km,
        convert_km(total_distance::numeric, 'km') AS total_distance_km,
    	(total_distance / duration) * 60 / distance_factor AS avg_speed_km
    FROM t
    JOIN cars c ON t.car_id = c.id
)
SELECT 
    ROUND(cast(avg_speed_km AS numeric) / 5, 0) * 5 AS avg_speed_km, 
    avg(efficiency) AS efficiency, 
    avg(consumption_km) AS consumption_km, 
    avg(total_distance_km) AS avg_distance_km,
    min(total_distance_km) AS min_distance_km, 
    max(total_distance_km) AS max_distance_km
FROM data
GROUP BY 1
ORDER BY 1 DESC;

[jirisykora83]

Don't drives include just a single value for the whole drive?

If so then, if drive is 200km then then it will be counted just for example for 115km/h even if parts for example 100km is at 130km/m and rest is less.

So, if it has a lot of drives then the result could be close (for speed which it shows) but you most likely never got consumption for 140km/h etc. because there will be no drives when AVG would be 140.

--

I check the query against and it assumed positions entries are exactly after 1min which are not so whole query is wrong (my/gpt query)

[merlinfive]

Hi , cool dashboard , why does it stop at 140Km/h ? , to less data ?

[jheredianet]

Hey, have in mind that those values are the average speed of a drive. So, in your case the top average speed of all your drives is 140km/h.
You can change the range of time (by default it's 10 years) or the min distance per drive (by default 1km) if you want to brose some specific drive or drives.

[merlinfive]

so I'm still to slow ;-) , waiting for my Track Pack (Top speed then 322Km/h) , I will do some testing during the next days

[jheredianet]

HI @jirisykora83 Finally I find the time to do this.
I've fine-tuned the query you suggested and redesigned all the dashboards. May I kindly ask you to check it out and suggests some improve or change?
For now, it's on the "develop" branch before making a release: https://github.com/jheredianet/Teslamate-CustomGrafanaDashboards/tree/develop

It should look something like this:

[jirisykora83]

Hi @jheredianet wow i want to try it but got this error:

db query error: pq: smallint out of range

It is for "Consumption by speed XXX" block

[jheredianet]

Hi @jirisykora83
I cannot reproduce that error in my environment. Could you try changing diferente periods of time? or distance or min speed.
I could be something related with your database, but i cannot tell you what.

Could you run that query outside Grafana in order to catch the error?

[jirisykora83]

SUM(CAST(power as INTEGER) * speed / 60.0) AS total_energy_consumed, this seems to be able fix the query for me

[jheredianet]

Could you try also. speed::integer ?

[jirisykora83]

that works too nice. I play with dashboard now and it seems cool nice work :)

Maybe it would be good adding graph with consumption (which also could be interpreted as range)

something like this.

with X: Speed
and Y: Avg. Consumption
by applying same flat/downhill as you have on the top.

And a few other improvements would be adding a temperature range selector and maybe AC/heating if that is available in data.

But again, this looks really well.

[jirisykora83]

also, i am thinking about excluding regen? As its generally peak in lower speed and i am not sure if it makes lower speed relevant but i am not sure about it

[jheredianet]

About the Graph it's a good idea, but it's difficult since standard Grafana charts are for timeline representation, so the X axis must a time field. There are plugins for the graph you are suggesting but it means to install it and make some other steps to use them, and for people not in IT world just using Teslamate could be a litte scary :-)

I'll make the release as it is as I figure out how to incorporate it in the future.

About the other improvements and not sure about how to add the AC/Heating and mix it. About regen the same, I'm not sure it's going to change the whole picture and analysis.
Thanks for your ideas.

[Arquiteto]

The numbers are wrong in this dashboard. I think that average power works fine, but average consumption is not right. Why? When you are travelling at 100km/h for an hour with average power of 20 you will use 20kWh and travel 100km. So your average consumption will be 200Wh/km. Now - if you're travelling at 50km/h for an hour with average power of 7kW you will use 7kWh but you will only travel 50km so your average consumption will be 140Wh/km. As you can see on the screen with this dashboard the average consumption is average power *10 for each speed.

[jheredianet]

I don't really understand what you mean. Please could you share a complete screenshot (with the distance) pointing how the avg. power should be? Thanks.

[Arquiteto]

This are the numbers I get from the original dashboard:

This are the numbers I get after I divided the consumption by average speed.

Look at the original numbers.
When I drive at 140km/h the average power is 40kW.
1h * 40kW = 40kWh
1h * 140km/h = 140km
If I would drive for 1 hour I would use 40kWh and drove 140km.

40kWh/140km = 40 000Wh/140km = 286Wh/km
So per km I would use 286Wh, not 400Wh that I get in the original dashboard. This is confirmed by the real consumption reported by Tesla when I drive it.

Also the numbers at low speeds are just ridiculously low, basically because the math just doesn't add up. At 50km/h with average power of 8kW when I travel for 1 hours I would use 8kWh and travel 50km. So it's 160Wh/km, not 78Wh/km.

[jheredianet]

Thank you @Arquiteto for the explanation. I've updated the dashboard. Could you test it please?
The update is just in the "develop" branch https://github.com/jheredianet/Teslamate-CustomGrafanaDashboards/blob/develop/dashboards/SpeedRates.json (you may import it manually just to test). If it's OK, it would be included in the next release.

[Arquiteto]

Yes, now the numbers look good.

[jheredianet]

Yes, now the numbers look good.

Thank you for your feedback. It would be implemented in the next release.
https://github.com/jheredianet/Teslamate-CustomGrafanaDashboards/releases/tag/v2024.01.22