Charging Cost Stats: Display KM for Home Charging and Supercharging

[coryschak]

Is it possible to make another stat visualization on the "Charging Cost Stats" page to display the amount of KM driven for supercharging and another stat for km driven thats home charging, similar to "Free KM Driven"

This would be handy to see how many KM put on from supercharges vs home charging for cost comparison.

[jheredianet]

Hi, it's a good idea specially for "supercharing" stats, but from "Home" it would be quite difficult because not English-speaking people name their home base "Home". I'll need to think how to manage that.

[coryschak]

Maybe even AC distance and DC distance would work for easier calculation, being a tesla DC is going to be supercharger 99% of the time and AC being home, just a thought.

I know everyone is different but I feel that's the norm with the charging infrastructure in place for our cars.

[jheredianet]

Not sure about that. In Europe we have different charger operator for DCs, and maybe I could see that 50% for other providers and 50% for Superchargers. The same as home, not all AC charging session or most of all are at home. I know even people that does not have a Wallbox at home, they usually do at mall of public places (destination chargers or other options).
I see easy for having the stats of distance driven for "Superchargers" since they are named so all around the world, but "Home" is another history.

Let me analyse further because these dashboards must remain useful for any user in any country.

[coryschak]

Forsure, if I come up with any other ideas to try to make it universal for all I'll post it here

[coryschak]

What if you do a drop down box similar to your page "Tracking Drives" where it has the "journey" drop down.

Instead it lists all the charging locations and you can select anyone of them to see combined milage for that location? More dynamic and users can choose what they want to see that's not perminantly hard-coded.

[jheredianet]

Yes, it's a good idea. I was thinking on using a variable to let the use write the location/geofence, but using a Dropdown box would be better. So It can show the top 10 geofences or so.

[jheredianet]

finally it's Done! in the developer brunch https://github.com/jheredianet/Teslamate-CustomGrafanaDashboards/tree/develop

Could you check it, if it works for you? If so, I will merge it to the main brunch to release a new version.

Here's how it should look; you may change the geofence from the dropdown list on the top. The distance added at Suc is estimated as well.

[coryschak]

Looks good for the "Home" geofence selection.
My Charging costs are incorrect, i had to edit the previous page to make it work correctly..

By default with your code i get the following

I changed the variable "ACCost" line:

SELECT
coalesce(sum(cp.cost) , 0)

to:
SELECT
coalesce(sum(cp.cost) - $DCCost , 0)

Variable "DCCost" from:

WITH data AS (
	SELECT c.charging_process_id 
	FROM charges c
	LEFT JOIN charging_processes cp ON cp.id = c.charging_process_id
	WHERE 
              cp.car_id = $car_id AND c.charger_voltage = 2
             AND c.fast_charger_present IS true
	GROUP BY 1
)
SELECT
        coalesce(sum(cp.cost) - $SucCost , 0)	
FROM
	charging_processes cp, data
WHERE
  $__timeFilter(end_date)
  AND cp.id IN (data.charging_process_id);

to

WITH data AS (
	SELECT c.charging_process_id 
	FROM charges c
	LEFT JOIN charging_processes cp ON cp.id = c.charging_process_id
	WHERE 
              cp.car_id = $car_id 
             AND c.fast_charger_present IS true
	GROUP BY 1
)
SELECT
        coalesce(sum(cp.cost) , 0)	
FROM
	charging_processes cp, data
WHERE
  $__timeFilter(end_date)
  AND cp.id IN (data.charging_process_id);

SUCCOST From

WITH data AS (
	SELECT c.charging_process_id 
	FROM charges c
	LEFT JOIN charging_processes cp ON cp.id = c.charging_process_id
	LEFT JOIN addresses addr ON addr.id = cp.address_id
	LEFT JOIN geofences geo ON geo.id = geofence_id
	WHERE 
	        cp.car_id = $car_id AND c.fast_charger_present IS true
		AND c.charger_voltage = 2
		AND COALESCE(geo.name, CONCAT_WS(', ', COALESCE(addr.name, nullif(CONCAT_WS(' ', addr.display_name), '')))) ILIKE '%supercharger%'
	GROUP BY 1
)
SELECT
        coalesce(sum(cp.cost), 0)
FROM
	charging_processes cp, data
WHERE
  $__timeFilter(end_date)
  AND cp.id IN (data.charging_process_id);

to:

WITH data AS (
	SELECT c.charging_process_id 
	FROM charges c
	LEFT JOIN charging_processes cp ON cp.id = c.charging_process_id
	LEFT JOIN addresses addr ON addr.id = cp.address_id
	LEFT JOIN geofences geo ON geo.id = geofence_id
	WHERE 
	        cp.car_id = $car_id AND c.fast_charger_present IS true
		AND COALESCE(geo.name, CONCAT_WS(', ', COALESCE(addr.name, nullif(CONCAT_WS(' ', addr.display_name), '')))) ILIKE '%supercharger%'
	GROUP BY 1
)
SELECT
        coalesce(sum(cp.cost), 0)
FROM
	charging_processes cp, data
WHERE
  $__timeFilter(end_date)
  AND cp.id IN (data.charging_process_id);

Not sure if this is the best way but made my totals add up correctly as all i cared about was difference for home charging (AC Charing) vs All others (DC Stations)

[jheredianet]

I don't get it what is wrong.

For the estimation of all AC charges, this is the Query, which consider all not null Cost and c.charger_voltage != 2 (Because report Voltage =2 when charging on DC)

The same for SuC, it considers c.charger_voltage = 2 AND c.fast_charger_present IS true. Then those totals are used to get individual calculations. I've tried with some other friends and myself and estimations are fine.

Could you just point the Query that it's wrong in order to understand what you mean?

To make it easy to test, try with a short time of period, just a few days or a trip, so you we can realize what is wrong and share your complete screen shot as this (it's mine of a trip in just one day, all estimations are Ok, as you can see and compare with the Top 10 charges table below the stats):