Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Timeline dashboard does not show drives and charges #4496

Open
1 task done
hjx900 opened this issue Jan 22, 2025 · 11 comments
Open
1 task done

Timeline dashboard does not show drives and charges #4496

hjx900 opened this issue Jan 22, 2025 · 11 comments
Labels
area:dashboard Related to a Grafana dashboard note:more information needed The reporter has to provide more information Stale

Comments

@hjx900
Copy link

hjx900 commented Jan 22, 2025

Is there an existing issue for this?

  • I have searched the existing issues

What happened?

After sometime back to 2023 Jul's update, the timeline only shows update activities, all those parking, driving, charging etc. are not visible...

can anybody help here to resolve the issue?

Image

Expected Behavior

No response

Steps To Reproduce

No response

Relevant log output

n/a

Screenshots

No response

Additional data

No response

Type of installation

Docker

Version

1.32.0
@DrMichael
Copy link
Collaborator

See #4189 (comment)

@swiffer
Copy link
Contributor

swiffer commented Jan 26, 2025

@hjx900 - any unclosed drives / charges / updates? As @DrMichael asked could you share some raw data?

@hjx900
Copy link
Author

hjx900 commented Jan 26, 2025

@hjx900 - any unclosed drives / charges / updates? As @DrMichael asked could you share some raw data?

dont think there is any unclosed ones... rest data are correct, except timeline, and location(just found)..

not sure if DrMichael asked but below is the screenshot.
Image

@DrMichael
Copy link
Collaborator

Right, now click on "Query"...

@hjx900
Copy link
Author

hjx900 commented Jan 27, 2025

Right, now click on "Query"...

Then that's what I replied to you the other day..

`-- CTE is used in Parking Query
with drives_and_charging_processes as (

select 'Drive' as activity, d.start_date, d.end_date, d.start_position_id, d.end_position_id, d.end_address_id, d.end_geofence_id, d.start_rated_range_km, d.end_rated_range_km, d.car_id, d.outside_temp_avg from drives d

union all

select 'Charging Process' as activity, cp.start_date, cp.end_date, cp.position_id as start_position_id, cp.position_id as end_position_id, cp.address_id as end_address_id, cp.geofence_id as end_geofence_id, cp.start_rated_range_km, cp.end_rated_range_km, cp.car_id, cp.outside_temp_avg from charging_processes cp

)

SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'🚗 Driving' AS "Action",
drives.duration_min AS "Duration",
CASE WHEN start_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(start_geofence_id, '/edit')
END AS start_path,
CASE WHEN end_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(end_geofence_id, '/edit')
END AS end_path,
COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address",
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address",
convert_km(end_km::NUMERIC, 'km') AS odometer_km,
convert_km(distance::NUMERIC, 'km') AS distance_km,
convert_km(end_rated_range_km::NUMERIC, 'km') AS end_range_km,
(end_rated_range_km - start_rated_range_km) * car.efficiency AS "kWh",
convert_km((end_rated_range_km - start_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
TP2.battery_level AS "SoC",
TP2.battery_level-TP1.battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/zm7wN6Zgz/drive-details?from=', ROUND(EXTRACT(EPOCH FROM start_date))*1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date))*1000, '&var-car_id=', drives.car_id, '&var-drive_id=', drives.id) AS slotlink
FROM drives
INNER JOIN cars AS car ON drives.car_id = car.id
INNER JOIN positions AS TP1 on drives.start_position_id = TP1.id
INNER JOIN positions AS TP2 on drives.end_position_id = TP2.id
INNER JOIN addresses start_address ON start_address_id = start_address.id
INNER JOIN addresses end_address ON end_address_id = end_address.id
LEFT JOIN geofences start_geofence ON start_geofence_id = start_geofence.id
LEFT JOIN geofences end_geofence ON end_geofence_id = end_geofence.id
WHERE
drives.start_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND drives.car_id = '1'
AND '🚗 Driving' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND
(COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT ILIKE'%%' or
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city))::TEXT ILIKE'%%')

UNION
SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'🔋 Charging' AS "Action",
charging_processes.duration_min AS "Duration",
CASE WHEN geofence_id IS NULL THEN CONCAT('new?lat=', address.latitude, '&lng=', address.longitude)
WHEN geofence_id IS NOT NULL THEN CONCAT(geofence_id, '/edit')
END AS start_path,
NULL AS end_path,
COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)) AS "Start Address",
'' AS "End Address",
convert_km(position.odometer::NUMERIC, 'km') AS odometer_km,
NULL AS distance_km,
convert_km(end_rated_range_km::NUMERIC, 'km') AS end_range_km,
charging_processes.charge_energy_added AS "kWh",
convert_km((end_rated_range_km - start_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
end_battery_level AS "SoC",
end_battery_level - start_battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/BHhxFeZRz/charge-details?from=', ROUND(EXTRACT(EPOCH FROM start_date)-10)*1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date)+10)*1000, '&var-car_id=', charging_processes.car_id, '&var-charging_process_id=', charging_processes.id) AS slotlink
FROM charging_processes
INNER JOIN positions AS position ON position_id = position.id
INNER JOIN addresses AS address ON address_id = address.id
LEFT JOIN geofences AS geofence ON geofence_id = geofence.id
WHERE
charging_processes.start_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND charging_processes.charge_energy_added > 0
AND charging_processes.car_id = '1'
AND '🔋 Charging' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city))::TEXT ILIKE'%%'
UNION
SELECT
d.end_date AS "Start",
LEAD(d.start_date) over w AS "End",
ROUND(EXTRACT(EPOCH FROM d.end_date)) * 1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM LEAD(d.start_date) over w))*1000 AS end_date_ts,
'🅿️ Parking' AS "Action",
EXTRACT(EPOCH FROM LEAD(d.start_date) over w - d.end_date)/60 AS "Duration",
CASE WHEN d.end_geofence_id IS NULL THEN CONCAT('new?lat=', end_position.latitude, '&lng=', end_position.longitude)
WHEN d.end_geofence_id IS NOT NULL THEN CONCAT(d.end_geofence_id, '/edit')
END AS start_path,
NULL AS end_path,
COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)) AS "Start Address",
'' AS "End Address",
convert_km(end_position.odometer::NUMERIC, 'km') AS odometer_km,
NULL AS distance_km,
convert_km(LEAD(d.start_rated_range_km) over w::NUMERIC, 'km') AS end_range_km,
((LEAD(d.start_rated_range_km) over w + (LEAD(start_position.odometer) over w - end_position.odometer)) - d.end_rated_range_km) * car.efficiency AS "kWh",
convert_km(((LEAD(d.start_rated_range_km) over w + (LEAD(start_position.odometer) over w - end_position.odometer)) - d.end_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
LEAD(start_position.battery_level) over w AS "SoC",
LEAD(start_position.battery_level) over w - end_position.battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/FkUpJpQZk/trip?from=', ROUND(EXTRACT(EPOCH FROM d.end_date))*1000, '&to=', ROUND(EXTRACT(EPOCH FROM LEAD(d.start_date) over w))*1000, '&var-car_id=', d.car_id) AS slotlink
FROM drives_and_charging_processes AS d
INNER JOIN cars AS car ON d.car_id = car.id
INNER JOIN positions AS start_position on d.start_position_id = start_position.id
INNER JOIN positions AS end_position on d.end_position_id = end_position.id
INNER JOIN addresses AS address ON d.end_address_id = address.id
LEFT JOIN geofences AS geofence ON d.end_geofence_id = geofence.id
WHERE
d.end_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND d.car_id='1'
AND '🅿️ Parking' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city))::TEXT ILIKE'%%'
WINDOW w as (ORDER BY d.start_date ASC)

UNION
SELECT
T1.end_date +(1 * interval '1 second') AS "Start", -- added 1 sec to get it after the corresponding Parking row
T2.start_date AS "End",
ROUND(EXTRACT(EPOCH FROM T1.end_date)) * 1000 - 1 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM T2.start_date)) * 1000 - 1 AS end_date_ts,
'❓ Missing' AS "Action",
-- EXTRACT(EPOCH FROM T2.start_date - T1.end_date)/60 AS "Duration",
NULL AS "Duration",
CASE WHEN T1.end_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude)
WHEN T1.end_geofence_id IS NOT NULL THEN CONCAT(T1.end_geofence_id, '/edit')
END AS start_path,
CASE WHEN T2.start_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude)
WHEN T2.start_geofence_id IS NOT NULL THEN CONCAT(T2.start_geofence_id, '/edit')
END AS end_path,
COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address",
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address",
convert_km(TP2.odometer::INTEGER, 'km') AS odometer_km,
convert_km((TP2.odometer - TP1.odometer)::INTEGER, 'km') AS distance_km,
convert_km(T2.end_rated_range_km::NUMERIC, 'km') AS end_range_km,
((TP2.rated_battery_range_km + (TP2.odometer - TP1.odometer)) - TP1.rated_battery_range_km) * car.efficiency AS "kWh",
convert_km(((TP2.rated_battery_range_km + (TP2.odometer - TP1.odometer)) - TP1.rated_battery_range_km)::INTEGER, 'km') AS range_diff_km,
NULL AS "SoC",
NULL AS "SoC Diff",
NULL AS outside_temp_avg_C,
NULL AS slotlink
-- TP2.battery_level AS "SoC",
-- TP2.battery_level-TP1.battery_level AS "SoC Diff",
-- (T1.outside_temp_avg+T2.outside_temp_avg)/2 AS outside_temp_avg_C
FROM drives AS T1
INNER JOIN cars AS car ON T1.car_id = car.id
INNER JOIN (SELECT d.*, LAG(id) OVER (ORDER BY id ASC) AS previous_id FROM drives d WHERE d.car_id = '1') AS T2 ON T1.id = T2.previous_id
INNER JOIN positions AS TP1 ON T1.end_position_id = TP1.id
INNER JOIN positions AS TP2 ON T2.start_position_id = TP2.id
INNER JOIN addresses AS start_address ON T1.end_address_id = start_address.id
INNER JOIN addresses AS end_address ON T2.start_address_id = end_address.id
LEFT JOIN geofences AS start_geofence ON T1.end_geofence_id = start_geofence.id
LEFT JOIN geofences AS end_geofence ON T2.start_geofence_id = end_geofence.id
WHERE
T1.end_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND TP2.odometer - TP1.odometer > 0.5
AND T1.end_address_id <> T2.start_address_id AND ((COALESCE(T1.end_geofence_id, 0) <> COALESCE(T2.start_geofence_id, 0)) OR (T1.end_geofence_id IS NULL AND T2.start_geofence_id IS NULL))
AND '❓ Missing' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND (
(COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT ILIKE'%%') or
(COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)))::TEXT ILIKE'%%')
UNION
SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'💾 Updating' AS "Action",
EXTRACT(EPOCH FROM end_date - start_date)/60 AS "Duration",
NULL AS start_path,
NULL AS end_path,
version AS "Start Address",
'' AS "End Address",
NULL AS odometer_km,
NULL AS distance_km,
NULL AS end_range_km,
NULL AS "kWh",
NULL AS range_diff_km,
NULL AS "SoC",
NULL AS "SoC Diff",
NULL AS outside_temp_avg_C,
CONCAT('https://www.notateslaapp.com/software-updates/version/', split_part(version, ' ', 1), '/release-notes') AS slotlink
FROM updates
WHERE
start_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND car_id = '1'
AND '💾 Updating' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND version::TEXT ILIKE'%%'

ORDER BY "Start" DESC;`

and the bottom info is
`Object
traceId:undefined
request:Object
url:"api/ds/query?ds_type=grafana-postgresql-datasource&requestId=Q103"
method:"POST"
data:Object
queries:Array[1]
0:Object
refId:"A"
datasource:Object
type:"grafana-postgresql-datasource"
uid:"PC98BA2F4D77E1A42"
rawSql:"-- CTE is used in Parking Query
with drives_and_charging_processes as (

select 'Drive' as activity, d.start_date, d.end_date, d.start_position_id, d.end_position_id, d.end_address_id, d.end_geofence_id, d.start_rated_range_km, d.end_rated_range_km, d.car_id, d.outside_temp_avg from drives d

union all

select 'Charging Process' as activity, cp.start_date, cp.end_date, cp.position_id as start_position_id, cp.position_id as end_position_id, cp.address_id as end_address_id, cp.geofence_id as end_geofence_id, cp.start_rated_range_km, cp.end_rated_range_km, cp.car_id, cp.outside_temp_avg from charging_processes cp

)

SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'🚗 Driving' AS "Action",
drives.duration_min AS "Duration",
CASE WHEN start_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(start_geofence_id, '/edit')
END AS start_path,
CASE WHEN end_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(end_geofence_id, '/edit')
END AS end_path,
COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address",
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address",
convert_km(end_km::NUMERIC, 'km') AS odometer_km,
convert_km(distance::NUMERIC, 'km') AS distance_km,
convert_km(end_rated_range_km::NUMERIC, 'km') AS end_range_km,
(end_rated_range_km - start_rated_range_km) * car.efficiency AS "kWh",
convert_km((end_rated_range_km - start_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
TP2.battery_level AS "SoC",
TP2.battery_level-TP1.battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/zm7wN6Zgz/drive-details?from=', ROUND(EXTRACT(EPOCH FROM start_date))*1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date))*1000, '&var-car_id=', drives.car_id, '&var-drive_id=', drives.id) AS slotlink
FROM drives
INNER JOIN cars AS car ON drives.car_id = car.id
INNER JOIN positions AS TP1 on drives.start_position_id = TP1.id
INNER JOIN positions AS TP2 on drives.end_position_id = TP2.id
INNER JOIN addresses start_address ON start_address_id = start_address.id
INNER JOIN addresses end_address ON end_address_id = end_address.id
LEFT JOIN geofences start_geofence ON start_geofence_id = start_geofence.id
LEFT JOIN geofences end_geofence ON end_geofence_id = end_geofence.id
WHERE
$__timeFilter(drives.start_date)
AND drives.car_id = '1'
AND '🚗 Driving' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND
(COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT ILIKE'%%' or
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city))::TEXT ILIKE'%%')

UNION
SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'🔋 Charging' AS "Action",
charging_processes.duration_min AS "Duration",
CASE WHEN geofence_id IS NULL THEN CONCAT('new?lat=', address.latitude, '&lng=', address.longitude)
WHEN geofence_id IS NOT NULL THEN CONCAT(geofence_id, '/edit')
END AS start_path,
NULL AS end_path,
COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)) AS "Start Address",
'' AS "End Address",
convert_km(position.odometer::NUMERIC, 'km') AS odometer_km,
NULL AS distance_km,
convert_km(end_rated_range_km::NUMERIC, 'km') AS end_range_km,
charging_processes.charge_energy_added AS "kWh",
convert_km((end_rated_range_km - start_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
end_battery_level AS "SoC",
end_battery_level - start_battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/BHhxFeZRz/charge-details?from=', ROUND(EXTRACT(EPOCH FROM start_date)-10)*1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date)+10)*1000, '&var-car_id=', charging_processes.car_id, '&var-charging_process_id=', charging_processes.id) AS slotlink
FROM charging_processes
INNER JOIN positions AS position ON position_id = position.id
INNER JOIN addresses AS address ON address_id = address.id
LEFT JOIN geofences AS geofence ON geofence_id = geofence.id
WHERE
$__timeFilter(charging_processes.start_date)
AND charging_processes.charge_energy_added > 0
AND charging_processes.car_id = '1'
AND '🔋 Charging' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city))::TEXT ILIKE'%%'
UNION
SELECT
d.end_date AS "Start",
LEAD(d.start_date) over w AS "End",
ROUND(EXTRACT(EPOCH FROM d.end_date)) * 1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM LEAD(d.start_date) over w))*1000 AS end_date_ts,
'🅿️ Parking' AS "Action",
EXTRACT(EPOCH FROM LEAD(d.start_date) over w - d.end_date)/60 AS "Duration",
CASE WHEN d.end_geofence_id IS NULL THEN CONCAT('new?lat=', end_position.latitude, '&lng=', end_position.longitude)
WHEN d.end_geofence_id IS NOT NULL THEN CONCAT(d.end_geofence_id, '/edit')
END AS start_path,
NULL AS end_path,
COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)) AS "Start Address",
'' AS "End Address",
convert_km(end_position.odometer::NUMERIC, 'km') AS odometer_km,
NULL AS distance_km,
convert_km(LEAD(d.start_rated_range_km) over w::NUMERIC, 'km') AS end_range_km,
((LEAD(d.start_rated_range_km) over w + (LEAD(start_position.odometer) over w - end_position.odometer)) - d.end_rated_range_km) * car.efficiency AS "kWh",
convert_km(((LEAD(d.start_rated_range_km) over w + (LEAD(start_position.odometer) over w - end_position.odometer)) - d.end_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
LEAD(start_position.battery_level) over w AS "SoC",
LEAD(start_position.battery_level) over w - end_position.battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/FkUpJpQZk/trip?from=', ROUND(EXTRACT(EPOCH FROM d.end_date))*1000, '&to=', ROUND(EXTRACT(EPOCH FROM LEAD(d.start_date) over w))*1000, '&var-car_id=', d.car_id) AS slotlink
FROM drives_and_charging_processes AS d
INNER JOIN cars AS car ON d.car_id = car.id
INNER JOIN positions AS start_position on d.start_position_id = start_position.id
INNER JOIN positions AS end_position on d.end_position_id = end_position.id
INNER JOIN addresses AS address ON d.end_address_id = address.id
LEFT JOIN geofences AS geofence ON d.end_geofence_id = geofence.id
WHERE
$__timeFilter(d.end_date)
AND d.car_id='1'
AND '🅿️ Parking' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city))::TEXT ILIKE'%%'
WINDOW w as (ORDER BY d.start_date ASC)

UNION
SELECT
T1.end_date +(1 * interval '1 second') AS "Start", -- added 1 sec to get it after the corresponding Parking row
T2.start_date AS "End",
ROUND(EXTRACT(EPOCH FROM T1.end_date)) * 1000 - 1 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM T2.start_date)) * 1000 - 1 AS end_date_ts,
'❓ Missing' AS "Action",
-- EXTRACT(EPOCH FROM T2.start_date - T1.end_date)/60 AS "Duration",
NULL AS "Duration",
CASE WHEN T1.end_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude)
WHEN T1.end_geofence_id IS NOT NULL THEN CONCAT(T1.end_geofence_id, '/edit')
END AS start_path,
CASE WHEN T2.start_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude)
WHEN T2.start_geofence_id IS NOT NULL THEN CONCAT(T2.start_geofence_id, '/edit')
END AS end_path,
COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address",
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address",
convert_km(TP2.odometer::INTEGER, 'km') AS odometer_km,
convert_km((TP2.odometer - TP1.odometer)::INTEGER, 'km') AS distance_km,
convert_km(T2.end_rated_range_km::NUMERIC, 'km') AS end_range_km,
((TP2.rated_battery_range_km + (TP2.odometer - TP1.odometer)) - TP1.rated_battery_range_km) * car.efficiency AS "kWh",
convert_km(((TP2.rated_battery_range_km + (TP2.odometer - TP1.odometer)) - TP1.rated_battery_range_km)::INTEGER, 'km') AS range_diff_km,
NULL AS "SoC",
NULL AS "SoC Diff",
NULL AS outside_temp_avg_C,
NULL AS slotlink
-- TP2.battery_level AS "SoC",
-- TP2.battery_level-TP1.battery_level AS "SoC Diff",
-- (T1.outside_temp_avg+T2.outside_temp_avg)/2 AS outside_temp_avg_C
FROM drives AS T1
INNER JOIN cars AS car ON T1.car_id = car.id
INNER JOIN (SELECT d.*, LAG(id) OVER (ORDER BY id ASC) AS previous_id FROM drives d WHERE d.car_id = '1') AS T2 ON T1.id = T2.previous_id
INNER JOIN positions AS TP1 ON T1.end_position_id = TP1.id
INNER JOIN positions AS TP2 ON T2.start_position_id = TP2.id
INNER JOIN addresses AS start_address ON T1.end_address_id = start_address.id
INNER JOIN addresses AS end_address ON T2.start_address_id = end_address.id
LEFT JOIN geofences AS start_geofence ON T1.end_geofence_id = start_geofence.id
LEFT JOIN geofences AS end_geofence ON T2.start_geofence_id = end_geofence.id
WHERE
$__timeFilter(T1.end_date)
AND TP2.odometer - TP1.odometer > 0.5
AND T1.end_address_id <> T2.start_address_id AND ((COALESCE(T1.end_geofence_id, 0) <> COALESCE(T2.start_geofence_id, 0)) OR (T1.end_geofence_id IS NULL AND T2.start_geofence_id IS NULL))
AND '❓ Missing' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND (
(COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT ILIKE'%%') or
(COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)))::TEXT ILIKE'%%')
UNION
SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'💾 Updating' AS "Action",
EXTRACT(EPOCH FROM end_date - start_date)/60 AS "Duration",
NULL AS start_path,
NULL AS end_path,
version AS "Start Address",
'' AS "End Address",
NULL AS odometer_km,
NULL AS distance_km,
NULL AS end_range_km,
NULL AS "kWh",
NULL AS range_diff_km,
NULL AS "SoC",
NULL AS "SoC Diff",
NULL AS outside_temp_avg_C,
CONCAT('https://www.notateslaapp.com/software-updates/version/', split_part(version, ' ', 1), '/release-notes') AS slotlink
FROM updates
WHERE
$__timeFilter(start_date)
AND car_id = '1'
AND '💾 Updating' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND version::TEXT ILIKE'%%'

ORDER BY "Start" DESC;"
format:"table"
datasourceId:1
intervalMs:300000
maxDataPoints:2703
queryCachingTTL:undefined
from:"1737369437034"
to:"1737974237034"
hideFromInspector:false
response:Object
results:Object
A:Object
status:200
frames:Array[1]
0:Object
schema:Object
refId:"A"
meta:Object
typeVersion:Array[0,0]
executedQueryString:"-- CTE is used in Parking Query
with drives_and_charging_processes as (

select 'Drive' as activity, d.start_date, d.end_date, d.start_position_id, d.end_position_id, d.end_address_id, d.end_geofence_id, d.start_rated_range_km, d.end_rated_range_km, d.car_id, d.outside_temp_avg from drives d

union all

select 'Charging Process' as activity, cp.start_date, cp.end_date, cp.position_id as start_position_id, cp.position_id as end_position_id, cp.address_id as end_address_id, cp.geofence_id as end_geofence_id, cp.start_rated_range_km, cp.end_rated_range_km, cp.car_id, cp.outside_temp_avg from charging_processes cp

)

SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'🚗 Driving' AS "Action",
drives.duration_min AS "Duration",
CASE WHEN start_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(start_geofence_id, '/edit')
END AS start_path,
CASE WHEN end_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(end_geofence_id, '/edit')
END AS end_path,
COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address",
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address",
convert_km(end_km::NUMERIC, 'km') AS odometer_km,
convert_km(distance::NUMERIC, 'km') AS distance_km,
convert_km(end_rated_range_km::NUMERIC, 'km') AS end_range_km,
(end_rated_range_km - start_rated_range_km) * car.efficiency AS "kWh",
convert_km((end_rated_range_km - start_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
TP2.battery_level AS "SoC",
TP2.battery_level-TP1.battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/zm7wN6Zgz/drive-details?from=', ROUND(EXTRACT(EPOCH FROM start_date))*1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date))*1000, '&var-car_id=', drives.car_id, '&var-drive_id=', drives.id) AS slotlink
FROM drives
INNER JOIN cars AS car ON drives.car_id = car.id
INNER JOIN positions AS TP1 on drives.start_position_id = TP1.id
INNER JOIN positions AS TP2 on drives.end_position_id = TP2.id
INNER JOIN addresses start_address ON start_address_id = start_address.id
INNER JOIN addresses end_address ON end_address_id = end_address.id
LEFT JOIN geofences start_geofence ON start_geofence_id = start_geofence.id
LEFT JOIN geofences end_geofence ON end_geofence_id = end_geofence.id
WHERE
drives.start_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND drives.car_id = '1'
AND '🚗 Driving' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND
(COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT ILIKE'%%' or
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city))::TEXT ILIKE'%%')

UNION
SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'🔋 Charging' AS "Action",
charging_processes.duration_min AS "Duration",
CASE WHEN geofence_id IS NULL THEN CONCAT('new?lat=', address.latitude, '&lng=', address.longitude)
WHEN geofence_id IS NOT NULL THEN CONCAT(geofence_id, '/edit')
END AS start_path,
NULL AS end_path,
COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)) AS "Start Address",
'' AS "End Address",
convert_km(position.odometer::NUMERIC, 'km') AS odometer_km,
NULL AS distance_km,
convert_km(end_rated_range_km::NUMERIC, 'km') AS end_range_km,
charging_processes.charge_energy_added AS "kWh",
convert_km((end_rated_range_km - start_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
end_battery_level AS "SoC",
end_battery_level - start_battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/BHhxFeZRz/charge-details?from=', ROUND(EXTRACT(EPOCH FROM start_date)-10)*1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date)+10)*1000, '&var-car_id=', charging_processes.car_id, '&var-charging_process_id=', charging_processes.id) AS slotlink
FROM charging_processes
INNER JOIN positions AS position ON position_id = position.id
INNER JOIN addresses AS address ON address_id = address.id
LEFT JOIN geofences AS geofence ON geofence_id = geofence.id
WHERE
charging_processes.start_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND charging_processes.charge_energy_added > 0
AND charging_processes.car_id = '1'
AND '🔋 Charging' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city))::TEXT ILIKE'%%'
UNION
SELECT
d.end_date AS "Start",
LEAD(d.start_date) over w AS "End",
ROUND(EXTRACT(EPOCH FROM d.end_date)) * 1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM LEAD(d.start_date) over w))*1000 AS end_date_ts,
'🅿️ Parking' AS "Action",
EXTRACT(EPOCH FROM LEAD(d.start_date) over w - d.end_date)/60 AS "Duration",
CASE WHEN d.end_geofence_id IS NULL THEN CONCAT('new?lat=', end_position.latitude, '&lng=', end_position.longitude)
WHEN d.end_geofence_id IS NOT NULL THEN CONCAT(d.end_geofence_id, '/edit')
END AS start_path,
NULL AS end_path,
COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)) AS "Start Address",
'' AS "End Address",
convert_km(end_position.odometer::NUMERIC, 'km') AS odometer_km,
NULL AS distance_km,
convert_km(LEAD(d.start_rated_range_km) over w::NUMERIC, 'km') AS end_range_km,
((LEAD(d.start_rated_range_km) over w + (LEAD(start_position.odometer) over w - end_position.odometer)) - d.end_rated_range_km) * car.efficiency AS "kWh",
convert_km(((LEAD(d.start_rated_range_km) over w + (LEAD(start_position.odometer) over w - end_position.odometer)) - d.end_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
LEAD(start_position.battery_level) over w AS "SoC",
LEAD(start_position.battery_level) over w - end_position.battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/FkUpJpQZk/trip?from=', ROUND(EXTRACT(EPOCH FROM d.end_date))*1000, '&to=', ROUND(EXTRACT(EPOCH FROM LEAD(d.start_date) over w))*1000, '&var-car_id=', d.car_id) AS slotlink
FROM drives_and_charging_processes AS d
INNER JOIN cars AS car ON d.car_id = car.id
INNER JOIN positions AS start_position on d.start_position_id = start_position.id
INNER JOIN positions AS end_position on d.end_position_id = end_position.id
INNER JOIN addresses AS address ON d.end_address_id = address.id
LEFT JOIN geofences AS geofence ON d.end_geofence_id = geofence.id
WHERE
d.end_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND d.car_id='1'
AND '🅿️ Parking' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city))::TEXT ILIKE'%%'
WINDOW w as (ORDER BY d.start_date ASC)

UNION
SELECT
T1.end_date +(1 * interval '1 second') AS "Start", -- added 1 sec to get it after the corresponding Parking row
T2.start_date AS "End",
ROUND(EXTRACT(EPOCH FROM T1.end_date)) * 1000 - 1 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM T2.start_date)) * 1000 - 1 AS end_date_ts,
'❓ Missing' AS "Action",
-- EXTRACT(EPOCH FROM T2.start_date - T1.end_date)/60 AS "Duration",
NULL AS "Duration",
CASE WHEN T1.end_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude)
WHEN T1.end_geofence_id IS NOT NULL THEN CONCAT(T1.end_geofence_id, '/edit')
END AS start_path,
CASE WHEN T2.start_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude)
WHEN T2.start_geofence_id IS NOT NULL THEN CONCAT(T2.start_geofence_id, '/edit')
END AS end_path,
COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address",
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address",
convert_km(TP2.odometer::INTEGER, 'km') AS odometer_km,
convert_km((TP2.odometer - TP1.odometer)::INTEGER, 'km') AS distance_km,
convert_km(T2.end_rated_range_km::NUMERIC, 'km') AS end_range_km,
((TP2.rated_battery_range_km + (TP2.odometer - TP1.odometer)) - TP1.rated_battery_range_km) * car.efficiency AS "kWh",
convert_km(((TP2.rated_battery_range_km + (TP2.odometer - TP1.odometer)) - TP1.rated_battery_range_km)::INTEGER, 'km') AS range_diff_km,
NULL AS "SoC",
NULL AS "SoC Diff",
NULL AS outside_temp_avg_C,
NULL AS slotlink
-- TP2.battery_level AS "SoC",
-- TP2.battery_level-TP1.battery_level AS "SoC Diff",
-- (T1.outside_temp_avg+T2.outside_temp_avg)/2 AS outside_temp_avg_C
FROM drives AS T1
INNER JOIN cars AS car ON T1.car_id = car.id
INNER JOIN (SELECT d.*, LAG(id) OVER (ORDER BY id ASC) AS previous_id FROM drives d WHERE d.car_id = '1') AS T2 ON T1.id = T2.previous_id
INNER JOIN positions AS TP1 ON T1.end_position_id = TP1.id
INNER JOIN positions AS TP2 ON T2.start_position_id = TP2.id
INNER JOIN addresses AS start_address ON T1.end_address_id = start_address.id
INNER JOIN addresses AS end_address ON T2.start_address_id = end_address.id
LEFT JOIN geofences AS start_geofence ON T1.end_geofence_id = start_geofence.id
LEFT JOIN geofences AS end_geofence ON T2.start_geofence_id = end_geofence.id
WHERE
T1.end_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND TP2.odometer - TP1.odometer > 0.5
AND T1.end_address_id <> T2.start_address_id AND ((COALESCE(T1.end_geofence_id, 0) <> COALESCE(T2.start_geofence_id, 0)) OR (T1.end_geofence_id IS NULL AND T2.start_geofence_id IS NULL))
AND '❓ Missing' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND (
(COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT ILIKE'%%') or
(COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)))::TEXT ILIKE'%%')
UNION
SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'💾 Updating' AS "Action",
EXTRACT(EPOCH FROM end_date - start_date)/60 AS "Duration",
NULL AS start_path,
NULL AS end_path,
version AS "Start Address",
'' AS "End Address",
NULL AS odometer_km,
NULL AS distance_km,
NULL AS end_range_km,
NULL AS "kWh",
NULL AS range_diff_km,
NULL AS "SoC",
NULL AS "SoC Diff",
NULL AS outside_temp_avg_C,
CONCAT('https://www.notateslaapp.com/software-updates/version/', split_part(version, ' ', 1), '/release-notes') AS slotlink
FROM updates
WHERE
start_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND car_id = '1'
AND '💾 Updating' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND version::TEXT ILIKE'%%'

ORDER BY "Start" DESC;"
fields:Array[19]
0:Object
name:"Start"
type:"time"
typeInfo:Object
frame:"time.Time"
nullable:true
1:Object
name:"End"
type:"time"
typeInfo:Object
frame:"time.Time"
nullable:true
2:Object
name:"start_date_ts"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
3:Object
name:"end_date_ts"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
4:Object
name:"Action"
type:"string"
typeInfo:Object
frame:"string"
nullable:true
5:Object
name:"Duration"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
6:Object
name:"start_path"
type:"string"
typeInfo:Object
frame:"string"
nullable:true
7:Object
name:"end_path"
type:"string"
typeInfo:Object
frame:"string"
nullable:true
8:Object
name:"Start Address"
type:"string"
typeInfo:Object
frame:"string"
nullable:true
9:Object
name:"End Address"
type:"string"
typeInfo:Object
frame:"string"
nullable:true
10:Object
name:"odometer_km"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
11:Object
name:"distance_km"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
12:Object
name:"end_range_km"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
13:Object
name:"kWh"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
14:Object
name:"range_diff_km"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
15:Object
name:"SoC"
type:"number"
typeInfo:Object
frame:"int16"
nullable:true
16:Object
name:"SoC Diff"
type:"number"
typeInfo:Object
frame:"int16"
nullable:true
17:Object
name:"outside_temp_avg_c"
type:"number"
typeInfo:Object
frame:"float64"
nullable:true
18:Object
name:"slotlink"
type:"string"
typeInfo:Object
frame:"string"
nullable:true
data:Object
values:Array[19]
0:Array[1737422308802]
1:Array[1737424724789]
2:Array[1737422309000]
3:Array[1737424725000]
4:Array[1]
0:"💾 Updating"
5:Array[40.26645]
6:Array[1]
0:null
7:Array[1]
0:null
8:Array[1]
0:"2024.45.32.1 ee93da10a358"
9:Array[1]
0:""
10:Array[1]
0:null
11:Array[1]
0:null
12:Array[1]
0:null
13:Array[1]
0:null
14:Array[1]
0:null
15:Array[1]
0:null
16:Array[1]
0:null
17:Array[1]
0:null
18:Array[1]
0:"https://www.notateslaapp.com/software-updates/version/2024.45.32.1/release-notes"
refId:"A"`

@DrMichael
Copy link
Collaborator

DrMichael commented Jan 27, 2025 •
edited
Loading

Ah, ok. Can you run a query in the database container? docker compose exec database psql -t teslamate -d teslamate in the docker-compose.yml directory and teslamate is the user and name of the database.

Assuming you had a drive in the last seven days, what gives

SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'🚗 Driving' AS "Action",
drives.duration_min AS "Duration",
CASE WHEN start_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(start_geofence_id, '/edit')
END AS start_path,
CASE WHEN end_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(end_geofence_id, '/edit')
END AS end_path,
COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address",
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address",
convert_km(end_km::NUMERIC, 'km') AS odometer_km,
convert_km(distance::NUMERIC, 'km') AS distance_km,
convert_km(end_rated_range_km::NUMERIC, 'km') AS end_range_km,
(end_rated_range_km - start_rated_range_km) * car.efficiency AS "kWh",
convert_km((end_rated_range_km - start_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
TP2.battery_level AS "SoC",
TP2.battery_level-TP1.battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/zm7wN6Zgz/drive-details?from=', ROUND(EXTRACT(EPOCH FROM start_date))*1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date))*1000, '&var-car_id=', drives.car_id, '&var-drive_id=', drives.id) AS slotlink
FROM drives
INNER JOIN cars AS car ON drives.car_id = car.id
INNER JOIN positions AS TP1 on drives.start_position_id = TP1.id
INNER JOIN positions AS TP2 on drives.end_position_id = TP2.id
INNER JOIN addresses start_address ON start_address_id = start_address.id
INNER JOIN addresses end_address ON end_address_id = end_address.id
LEFT JOIN geofences start_geofence ON start_geofence_id = start_geofence.id
LEFT JOIN geofences end_geofence ON end_geofence_id = end_geofence.id
WHERE
drives.start_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND drives.car_id = '1'
AND '🚗 Driving' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND
(COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT ILIKE'%%' or
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city))::TEXT ILIKE'%%');

@JakobLichterfeld
Copy link
Collaborator

After sometime back to 2023 Jul's update, the timeline only shows update activities, all those parking, driving, charging etc. are not visible...

I assume you played with the dashboard. Please do a backup, delete your grafana volume, run a docker compose pull followed by a docker compose up -d (if you see your drives in the drives dashboard).

@JakobLichterfeld JakobLichterfeld added note:more information needed The reporter has to provide more information area:dashboard Related to a Grafana dashboard labels Jan 28, 2025
@JakobLichterfeld JakobLichterfeld changed the title Timeline is not recorded and shown properly Timeline dashboard does not show drives and charges Jan 28, 2025
@hjx900
Copy link
Author

hjx900 commented Feb 6, 2025

After sometime back to 2023 Jul's update, the timeline only shows update activities, all those parking, driving, charging etc. are not visible...

I assume you played with the dashboard. Please do a backup, delete your grafana volume, run a docker compose pull followed by a docker compose up -d (if you see your drives in the drives dashboard).

Thank you and sorry for late response as just back from holiday..

I did tried to delete grafana volume and re-pull the image, it doens't help unfortunately. but i just completed so maybe i'll drive tomorrow and see if anything changed..

@hjx900
Copy link
Author

hjx900 commented Feb 6, 2025 •
edited
Loading

[>]([url](

)) Ah, ok. Can you run a query in the database container? docker compose exec database psql -t teslamate -d teslamate in the docker-compose.yml directory and teslamate is the user and name of the database.

Assuming you had a drive in the last seven days, what gives

SELECT
start_date AS "Start",
end_date AS "End",
ROUND(EXTRACT(EPOCH FROM start_date))*1000 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM end_date))*1000 AS end_date_ts,
'🚗 Driving' AS "Action",
drives.duration_min AS "Duration",
CASE WHEN start_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(start_geofence_id, '/edit')
END AS start_path,
CASE WHEN end_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude)
WHEN start_geofence_id IS NOT NULL THEN CONCAT(end_geofence_id, '/edit')
END AS end_path,
COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address",
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address",
convert_km(end_km::NUMERIC, 'km') AS odometer_km,
convert_km(distance::NUMERIC, 'km') AS distance_km,
convert_km(end_rated_range_km::NUMERIC, 'km') AS end_range_km,
(end_rated_range_km - start_rated_range_km) * car.efficiency AS "kWh",
convert_km((end_rated_range_km - start_rated_range_km)::NUMERIC, 'km') AS range_diff_km,
TP2.battery_level AS "SoC",
TP2.battery_level-TP1.battery_level AS "SoC Diff",
convert_celsius(outside_temp_avg, 'C') AS outside_temp_avg_C,
CONCAT('d/zm7wN6Zgz/drive-details?from=', ROUND(EXTRACT(EPOCH FROM start_date))*1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date))*1000, '&var-car_id=', drives.car_id, '&var-drive_id=', drives.id) AS slotlink
FROM drives
INNER JOIN cars AS car ON drives.car_id = car.id
INNER JOIN positions AS TP1 on drives.start_position_id = TP1.id
INNER JOIN positions AS TP2 on drives.end_position_id = TP2.id
INNER JOIN addresses start_address ON start_address_id = start_address.id
INNER JOIN addresses end_address ON end_address_id = end_address.id
LEFT JOIN geofences start_geofence ON start_geofence_id = start_geofence.id
LEFT JOIN geofences end_geofence ON end_geofence_id = end_geofence.id
WHERE
drives.start_date BETWEEN '2025-01-20T10:37:17.034Z' AND '2025-01-27T10:37:17.034Z'
AND drives.car_id = '1'
AND '🚗 Driving' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND
(COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT ILIKE'%%' or
COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city))::TEXT ILIKE'%%');

I copied the code you give in container, and it ask for further code...
Image

@DrMichael
Copy link
Collaborator

DrMichael commented Feb 7, 2025 •
edited
Loading

Now paste the above select statement into this...

(But that is not the latest version of the database container. It should have psql version 16.6!)

@github-actions GitHub Actions
Copy link
Contributor

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 7 days if no further activity occurs. Thank you for your contributions.

@github-actions github-actions bot added the Stale label Mar 10, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area:dashboard Related to a Grafana dashboard note:more information needed The reporter has to provide more information Stale
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@swiffer @DrMichael @JakobLichterfeld @hjx900