Hi, I tried with the query below - please try in your scenario. As we are calculating this based on positions data and ranges are only included for non streamed data (gathered every 15 seconds) I doubt in beeing that useful (most breaks should be shorter, so it's collecting only longer periods of driving downwards.

WITH data AS (
    SELECT
        p.car_id,
        lag(p.rated_battery_range_km) OVER (ORDER BY p.date) - p.rated_battery_range_km AS range_loss,
        p.odometer - lag(p.odometer) OVER (ORDER BY p.date) AS distance
    from positions p
    WHERE
      p.drive_id = {{DRIVE_ID_ENTER_HERE}} and ideal_battery_range_km is not null
    ORDER BY date
)

SELECT
  SUM(range_loss * car.efficiency * 1000) / convert_km(sum(distance)::numeric, 'km') as consumption_km
FROM data
  JOIN cars car on car.id = car_id
  WHERE range_loss < 0

Hey, first of all, thanks for the quick response.
I sadly couldnt get your query to output anything, the result was just empty.

But, i also think the query is not what i am searching for. I want the exact kWh power added/removed from the battery not the KM which theoretically got added/removed.

I tried to query this data on my own by getting the power for each second the car drove. which outputs exactly how much kW the car used/added per second (sometimes multiple entries per second, or only one per few seconds).

I tried it with a 58.06km drive, which teslamate showed me it used 10.60kWh of energy.
My query shows -9.18 kWh added by recuperation and 37.8 kWh used by driving/heating etc.
But these numbers look very wrong to me. I am not the most skilled with querying this data or writing a correct formula, so i will just leave my example here for recuperated energy, or maybe can someone tell me why this is not going to work and why i am totally wrong with what i am trying.

I tried this by going to Drives -> select one -> add new visualization and change style to Table.

SELECT
	SUM(kW * (1.0 / 3600)) AS total_kWh_recuperated
FROM (
	SELECT
		DATE_TRUNC('second', date) AS time,
		SUM(power) AS kW
	FROM
		positions
	WHERE
		car_id = $car_id
		AND $__timeFilter(date)
		AND power < 0
	GROUP BY
		time
) AS subquery

@jameskitt616 - thanks, going by power is better in that case as it's sampled as part of the streaming data and therefore shows short periods of regenerative breaking!

Going with an AVG instead of SUM within the subquery should provide better results. to correctly account for period length and not using ang AVG (and deal with periods longer than 1 seconds correctly) here is an updated query:

make sure to replace the drive id

with data as (
select
	case when power < 0 then 'regen' else 'use' end as action,
	p.date,
	p.power,
	extract (second from p.date - lag(p.date) over (order by p.date)) as seconds
from positions p
	where drive_id = 1519
)

select action, sum(power * (seconds / 3600)) from data where seconds is not null group by 1

testing this query on a drive i had this morning gives these results

i guess that energy consumed (net) is incl. heating / cooling etc. while going by power is engine power only.
that would explain the higher energy consumed (net) value.

happy to end it, think it's a great addition - as we are scanning positions for this query to work i think it should be included on drive details only for now (perf reasons). i suggest also adding a description on how the numbers are calculated and that it's important to have high data granularity for good results (e.g. enable streaming data).

would you like to open a PR or do you want me to take over?

add. comment: the query currently carries the last value forward. don't know if that is ok or if we should exclude periods of the drive where no data is sampled at all. for example if a new data point is missing for 3 seconds we exclude this power value from the results.

with data as (
select
	case when power < 0 then 'regen' else 'use' end as action,
	p.date,
	p.power,
	extract (second from p.date - lag(p.date) over (order by p.date)) as seconds
from positions p
	where drive_id = 1519
)

select action, sum(power * (seconds / 3600)) from data where seconds is not null and seconds < 3 group by 1

Thanks for the queries!
The numbers roughly match up. They are +- 2kWh off from the "Energy Used" I'd say thats already pretty good, and probably hard to even get more detailed, if even possible due to probably missing logged data per second.

feel free to tag me within the PR for reviewing / testing.

PR opened in #4386