The Timeline does not display properly when I upgrade to 1.30.1

[youguangbin]

version 1.30.1
The timeline cannot be shown driving charging parking but only updating.

[DrMichael]

What does the query look like? (->Inspect)

[hjx900]

What does the query look like? (->Inspect)

hi there, i got the same issue since 2023 Jul, only updating is shown...just check the query and seems only updating activities are selected to show. can you please help?

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_$length_unit,
NULL AS distance_$length_unit,
NULL AS end_range_$length_unit,
NULL AS "kWh",
NULL AS range_diff_$length_unit,
NULL AS "SoC",
NULL AS "SoC Diff",
NULL AS outside_temp_avg_$temp_unit,
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 = $car_id
AND '💾 Updating' in ($action_filter)
AND version::TEXT ILIKE'%$text_filter%'

ORDER BY "Start" DESC;

[DrMichael]

Sorry, that is neither what you get with "Inspect->Query" nor is it the query under "Edit".

[hjx900]

Is this one correct?

-- 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-15T10:05:26.816Z' AND '2025-01-22T10:05:26.816Z'
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-15T10:05:26.816Z' AND '2025-01-22T10:05:26.816Z'
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-15T10:05:26.816Z' AND '2025-01-22T10:05:26.816Z'
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-15T10:05:26.816Z' AND '2025-01-22T10:05:26.816Z'
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-15T10:05:26.816Z' AND '2025-01-22T10:05:26.816Z'
AND car_id = '1'
AND '💾 Updating' in ('🚗 Driving','🔋 Charging','🅿️ Parking','❓ Missing','💾 Updating')
AND version::TEXT ILIKE'%%'

ORDER BY "Start" DESC;

[DrMichael]

That looks ok. What gives Inspect->Query and Inspect->Data?

[hjx900]

not sure how can i get those details.. the screenshot is what i suppose you asked..
!