Change historacal charge price

[Bolten88]

Hello,

i accidentally set the charging price of a geofence location to €30 instead of €0.30. The total price in grafana of charging at that location are completely wrong now. Can i change the price of the previous charging sessions from that location?

Kind regards,
Mark

[DrMichael]

I am sure, that that can be done with some database magic...

[sparvata]

get the id from the charges table and you can do like the below if the charge id is 9 that you want to modify the cost for. In the query below, if you set the cost to NULL instead of a value, you can edit it from the web UI I think.

docker-compose exec database psql teslamate teslamate << .
SELECT * FROM charging_processes where id = 9;
update charging_processes set cost = 0.30 where id = '9';
.

[DrMichael]

Ok, that challenged me...
Assuming your geofence is named 'Zuhause', you can divide all charging prices at that location by 100 with:

update charging_processes
	set cost = cost/100
from geofences
where charging_processes.geofence_id = geofences.id AND geofences.name='Zuhause'

(use that instead of line 2 and 3 above)

[sparvata]

note that, if you want to make the change for all charges at that geolocation you use the geofence_id. If you need to change for a specific charge, you can use the id from the charges table.

[DrMichael]

And don't forget to make a backup before...

[Bolten88]

Thanks All, this is going to work. Thanks for pointing me in the right direction. And sure i do have backups :)

[damccull]

I just started looking into this. It looks like price per kWh is calculated realtime, while the database stores the total cost and energy added. Is that correct? Going to need that info to make the correct updates, because it's looking like I'll need a query like this:

UPDATE charging_processes SET cost = charge_energy_added * 0.39 WHERE geofence_id=1 AND start_date >= '2023-08-01 00:00:00.000' AND start_date <= '2023-08-31 23:59:59.999';

Is this correct, assuming I want to update all August 2023 charges to 39 cents per kWh?

[khorton]

This worked perfectly for me. But, the cost calculation should be based on the electricity used, which is a bit more than the electricity added.

docker compose exec -T database psql -d teslamate -U teslamate << . UPDATE charging_processes SET cost = charge_energy_used * 0.39 WHERE geofence_id=1 AND start_date >= '2023-08-01 00:00:00.000' AND start_date <= '2023-08-31 23:59:59.999';