Skip to content

Instantly share code, notes, and snippets.

@to-masz

to-masz/RoadTrip.json Secret

Last active April 18, 2024 08:33
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save to-masz/d946e716c83f028b1a90319d7a404678 to your computer and use it in GitHub Desktop.
Save to-masz/d946e716c83f028b1a90319d7a404678 to your computer and use it in GitHub Desktop.
Download ZIP
[WIP] Custom trip dashboard for TeslaMate/Grafana
Raw
RoadTrip.json
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "datasource",
"uid": "grafana"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"target": {
"limit": 100,
"matchAny": false,
"tags": [],
"type": "dashboard"
},
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 1,
"id": 28,
"links": [
{
"asDropdown": false,
"icon": "doc",
"includeVars": false,
"keepTime": false,
"tags": [],
"targetBlank": false,
"title": "Adjust time to drives",
"tooltip": "",
"type": "link",
"url": "/d/bdh3rtgqmblkwc?from=$from&to=$to"
},
{
"icon": "dashboard",
"tags": [],
"title": "TeslaMate",
"tooltip": "",
"type": "link",
"url": "[[base_url:raw]]"
},
{
"asDropdown": true,
"icon": "external link",
"tags": [
"tesla"
],
"title": "Dashboards",
"type": "dashboards"
}
],
"liveNow": false,
"panels": [
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"fieldConfig": {
"defaults": {
"color": {
"fixedColor": "dark-blue",
"mode": "fixed"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 100,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"insertNulls": false,
"lineInterpolation": "linear",
"lineWidth": 0,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "never",
"spanNulls": true,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"max": 100,
"noValue": "0",
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "percent"
},
"overrides": [
{
"matcher": {
"id": "byFrameRefID",
"options": "SPEED"
},
"properties": [
{
"id": "max",
"value": 200
},
{
"id": "custom.axisPlacement",
"value": "right"
},
{
"id": "unit",
"value": "velocitykmh"
},
{
"id": "custom.axisColorMode",
"value": "series"
},
{
"id": "color",
"value": {
"fixedColor": "#c8c8c8",
"mode": "fixed"
}
},
{
"id": "custom.lineWidth",
"value": 1
},
{
"id": "custom.fillOpacity",
"value": 0
}
]
},
{
"matcher": {
"id": "byFrameRefID",
"options": "parking"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "super-light-blue",
"mode": "fixed"
}
},
{
"id": "custom.fillOpacity",
"value": 30
},
{
"id": "displayName"
}
]
},
{
"matcher": {
"id": "byFrameRefID",
"options": "SUC"
},
"properties": [
{
"id": "custom.fillOpacity",
"value": 100
},
{
"id": "custom.lineWidth",
"value": 0
}
]
},
{
"matcher": {
"id": "byName",
"options": "charging (AC)"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "green",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "charging (DC)"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "orange",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 11,
"w": 13,
"x": 0,
"y": 0
},
"id": 1,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "(\n SELECT $__timeGroup(date, '5s'), \n avg(battery_level) as idle\n FROM positions\n WHERE date BETWEEN ($__timeFrom()::timestamp - interval '1 day') AND ($__timeTo()::timestamp + interval '1 day') AND car_id = $car_id\n GROUP BY 1\n) union all \n(\n SELECT $__timeGroup(date, '5s'), avg(battery_level) as parking\n FROM charges c\n LEFT JOIN charging_processes p ON c.charging_process_id = p.id\n WHERE date BETWEEN ($__timeFrom()::timestamp - interval '1 day') AND ($__timeTo()::timestamp + interval '1 day') \n AND p.car_id = $car_id\n GROUP BY 1\n)\nORDER BY 1",
"refId": "parking",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "(\n SELECT $__timeGroup(date, '5s'), \n case when drive_id is not null then avg(battery_level) else 0 end as driving\n FROM positions\n WHERE date BETWEEN ($__timeFrom()::timestamp - interval '1 day') AND ($__timeTo()::timestamp + interval '1 day') AND car_id = $car_id\n GROUP BY 1, drive_id\n) \nUNION ALL (\n SELECT $__timeGroup(start_date, '5s')-0.1, 0 as driving\n FROM drives d \n WHERE (\n (start_date > ($__timeFrom()::timestamp - interval '1 day') AND start_date < ($__timeTo()::timestamp + interval '1 day'))\n OR \n (end_date > ($__timeFrom()::timestamp - interval '1 day') AND end_date < ($__timeTo()::timestamp + interval '1 day'))\n ) AND car_id = $car_id \n)\nUNION ALL (\n SELECT $__timeGroup(end_date, '5s') + 0.1, 0 as driving\n FROM drives\n WHERE (\n (start_date > ($__timeFrom()::timestamp - interval '1 day') AND start_date < ($__timeTo()::timestamp + interval '1 day'))\n OR \n (end_date > ($__timeFrom()::timestamp - interval '1 day') AND end_date < ($__timeTo()::timestamp + interval '1 day'))\n ) AND car_id = $car_id \n)\nORDER BY 1",
"refId": "SOC",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "time_series",
"group": [],
"hide": false,
"metricColumn": "none",
"rawQuery": true,
"rawSql": "(\n SELECT $__timeGroup(date, '5s') as time, \n CASE WHEN NULLIF(mode() within group (order by charger_phases),0) is null \n THEN avg(battery_level)\n\t\t\tELSE 0\n\t\tEND AS \"charging (DC)\",\n CASE WHEN NULLIF(mode() within group (order by charger_phases),0) is null \n THEN 0\n\t\t\tELSE avg(battery_level)\n\t\tEND AS \"charging (AC)\"\n FROM charges c\n LEFT JOIN charging_processes p ON c.charging_process_id = p.id\n WHERE date BETWEEN ($__timeFrom()::timestamp - interval '1 day') AND ($__timeTo()::timestamp + interval '1 day') \n AND p.car_id = $car_id\n GROUP BY 1\n)\nUNION ALL (\n SELECT $__timeGroup(start_date, '5s')-0.1, 0, 0\n FROM charging_processes\n WHERE (\n (start_date > ($__timeFrom()::timestamp - interval '1 day') AND start_date < ($__timeTo()::timestamp + interval '1 day'))\n OR \n (end_date > ($__timeFrom()::timestamp - interval '1 day') AND end_date < ($__timeTo()::timestamp + interval '1 day'))\n ) AND car_id = $car_id \n)\nUNION ALL (\n SELECT $__timeGroup(end_date, '5s') + 0.1, 0, 0\n FROM charging_processes\n WHERE (\n (start_date > ($__timeFrom()::timestamp - interval '1 day') AND start_date < ($__timeTo()::timestamp + interval '1 day'))\n OR \n (end_date > ($__timeFrom()::timestamp - interval '1 day') AND end_date < ($__timeTo()::timestamp + interval '1 day'))\n ) AND car_id = $car_id \n)\nORDER BY 1",
"refId": "SUC",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "(\n SELECT $__timeGroup(date, '5s'), \n avg(convert_km(speed::numeric, 'km')) as speed\n FROM positions\n WHERE date BETWEEN ($__timeFrom()::timestamp - interval '1 day') AND ($__timeTo()::timestamp + interval '1 day') \n AND car_id = $car_id AND drive_id IS NOT NULL\n GROUP BY 1\n) \nUNION ALL (\n SELECT $__timeGroup(start_date, '5s')-1, 0 as speed\n FROM drives d \n WHERE (\n (start_date > ($__timeFrom()::timestamp - interval '1 day') AND start_date < ($__timeTo()::timestamp + interval '1 day'))\n OR \n (end_date > ($__timeFrom()::timestamp - interval '1 day') AND end_date < ($__timeTo()::timestamp + interval '1 day'))\n ) AND car_id = $car_id \n)\nUNION ALL (\n SELECT $__timeGroup(end_date, '5s') + 0.1, 0 as speed\n FROM drives\n WHERE (\n (start_date > ($__timeFrom()::timestamp - interval '1 day') AND start_date < ($__timeTo()::timestamp + interval '1 day'))\n OR \n (end_date > ($__timeFrom()::timestamp - interval '1 day') AND end_date < ($__timeTo()::timestamp + interval '1 day'))\n ) AND car_id = $car_id \n)\nORDER BY 1",
"refId": "SPEED",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byRegexp",
"options": "/latitude|longitude/"
},
"properties": [
{
"id": "custom.hideFrom",
"value": {
"legend": false,
"tooltip": true,
"viz": false
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "duration"
},
"properties": [
{
"id": "unit",
"value": "dthms"
},
{
"id": "displayName",
"value": "Duration"
}
]
},
{
"matcher": {
"id": "byName",
"options": "energy_added"
},
"properties": [
{
"id": "unit",
"value": "kwatt"
},
{
"id": "displayName",
"value": "Energy added"
}
]
},
{
"matcher": {
"id": "byName",
"options": "time"
},
"properties": [
{
"id": "displayName",
"value": "Time"
}
]
},
{
"matcher": {
"id": "byName",
"options": "after"
},
"properties": [
{
"id": "displayName",
"value": "After"
}
]
},
{
"matcher": {
"id": "byName",
"options": "before"
},
"properties": [
{
"id": "displayName",
"value": "Before"
}
]
}
]
},
"gridPos": {
"h": 18,
"w": 11,
"x": 13,
"y": 0
},
"id": 6,
"maxDataPoints": 500,
"options": {
"basemap": {
"config": {
"showLabels": true,
"theme": "auto"
},
"name": "Layer 0",
"opacity": 1,
"type": "carto"
},
"controls": {
"mouseWheelZoom": true,
"showAttribution": true,
"showDebug": false,
"showMeasure": false,
"showScale": false,
"showZoom": true
},
"layers": [
{
"config": {
"arrow": 0,
"style": {
"color": {
"fixed": "blue"
},
"lineWidth": 2,
"opacity": 1,
"rotation": {
"fixed": 0,
"max": 360,
"min": -360,
"mode": "mod"
},
"size": {
"fixed": 3,
"max": 15,
"min": 2
},
"symbol": {
"fixed": "img/icons/marker/circle.svg",
"mode": "fixed"
},
"symbolAlign": {
"horizontal": "center",
"vertical": "center"
},
"textConfig": {
"fontSize": 12,
"offsetX": 0,
"offsetY": 0,
"textAlign": "center",
"textBaseline": "middle"
}
}
},
"filterData": {
"id": "byRefId",
"options": "A"
},
"name": "Layer 1",
"tooltip": true,
"type": "route"
},
{
"config": {
"showLegend": false,
"style": {
"color": {
"fixed": "super-light-blue"
},
"opacity": 1,
"rotation": {
"fixed": 0,
"max": 360,
"min": -360,
"mode": "mod"
},
"size": {
"fixed": 10,
"max": 15,
"min": 2
},
"symbol": {
"fixed": "img/icons/marker/circle.svg",
"mode": "fixed"
},
"symbolAlign": {
"horizontal": "center",
"vertical": "center"
},
"textConfig": {
"fontSize": 12,
"offsetX": 0,
"offsetY": 0,
"textAlign": "center",
"textBaseline": "middle"
}
}
},
"filterData": {
"id": "byRefId",
"options": "parking"
},
"location": {
"mode": "auto"
},
"name": "Idle",
"tooltip": true,
"type": "markers"
},
{
"config": {
"showLegend": false,
"style": {
"color": {
"fixed": "#ffffff"
},
"opacity": 1,
"rotation": {
"fixed": 0,
"max": 360,
"min": -360,
"mode": "mod"
},
"size": {
"fixed": 5,
"max": 15,
"min": 2
},
"symbol": {
"fixed": "https://upload.wikimedia.org/wikipedia/commons/5/5f/Parking_icon.svg",
"mode": "fixed"
},
"symbolAlign": {
"horizontal": "center",
"vertical": "center"
},
"textConfig": {
"fontSize": 12,
"offsetX": 0,
"offsetY": 0,
"textAlign": "center",
"textBaseline": "middle"
}
}
},
"filterData": {
"id": "byRefId",
"options": "parking-p"
},
"location": {
"mode": "auto"
},
"name": "Idle-P",
"tooltip": false,
"type": "markers"
},
{
"config": {
"showLegend": false,
"style": {
"color": {
"fixed": "orange"
},
"opacity": 1,
"rotation": {
"fixed": 0,
"max": 360,
"min": -360,
"mode": "mod"
},
"size": {
"fixed": 10,
"max": 15,
"min": 2
},
"symbol": {
"fixed": "img/icons/marker/circle.svg",
"mode": "fixed"
},
"symbolAlign": {
"horizontal": "center",
"vertical": "center"
},
"textConfig": {
"fontSize": 12,
"offsetX": 0,
"offsetY": 0,
"textAlign": "center",
"textBaseline": "middle"
}
}
},
"filterData": {
"id": "byRefId",
"options": "B"
},
"name": "Charging DC",
"opacity": 1,
"tooltip": true,
"type": "markers"
},
{
"config": {
"showLegend": false,
"style": {
"color": {
"fixed": "green"
},
"opacity": 1,
"rotation": {
"fixed": 0,
"max": 360,
"min": -360,
"mode": "mod"
},
"size": {
"fixed": 10,
"max": 15,
"min": 2
},
"symbol": {
"fixed": "img/icons/marker/circle.svg",
"mode": "fixed"
},
"symbolAlign": {
"horizontal": "center",
"vertical": "center"
},
"textConfig": {
"fontSize": 12,
"offsetX": 0,
"offsetY": 0,
"textAlign": "center",
"textBaseline": "middle"
}
}
},
"filterData": {
"id": "byRefId",
"options": "C"
},
"location": {
"mode": "auto"
},
"name": "Charging AC",
"tooltip": true,
"type": "markers"
},
{
"config": {
"showLegend": false,
"style": {
"color": {
"fixed": "light-blue"
},
"opacity": 1,
"rotation": {
"fixed": 0,
"max": 360,
"min": -360,
"mode": "mod"
},
"size": {
"fixed": 20,
"max": 15,
"min": 2
},
"symbol": {
"fixed": "img/icons/unicons/play-circle.svg",
"mode": "fixed"
},
"symbolAlign": {
"horizontal": "center",
"vertical": "center"
},
"textConfig": {
"fontSize": 12,
"offsetX": 0,
"offsetY": 0,
"textAlign": "center",
"textBaseline": "middle"
}
}
},
"filterData": {
"id": "byRefId",
"options": "D"
},
"location": {
"mode": "auto"
},
"name": "Layer 4",
"tooltip": false,
"type": "markers"
},
{
"config": {
"showLegend": false,
"style": {
"color": {
"fixed": "light-blue"
},
"opacity": 1,
"rotation": {
"fixed": 0,
"max": 360,
"min": -360,
"mode": "mod"
},
"size": {
"fixed": 20,
"max": 15,
"min": 2
},
"symbol": {
"fixed": "img/icons/unicons/stop-circle.svg",
"mode": "fixed"
},
"symbolAlign": {
"horizontal": "center",
"vertical": "center"
},
"textConfig": {
"fontSize": 12,
"offsetX": 0,
"offsetY": 0,
"textAlign": "center",
"textBaseline": "middle"
}
}
},
"filterData": {
"id": "byRefId",
"options": "E"
},
"location": {
"mode": "auto"
},
"name": "Layer 5",
"tooltip": false,
"type": "markers"
}
],
"tooltip": {
"mode": "details"
},
"view": {
"allLayers": true,
"id": "fit",
"lat": 0,
"lon": 0,
"zoom": 15
}
},
"pluginVersion": "10.4.1",
"targets": [
{
"datasource": "TeslaMate",
"editorMode": "code",
"format": "time_series",
"group": [],
"hide": false,
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT\n\t$__timeGroup(date, '5s') AS time,\n\tavg(latitude) AS latitude,\n\tavg(longitude) AS longitude\nFROM\n\tpositions\nWHERE\n car_id = $car_id AND\n\t$__timeFilter(date)\nGROUP BY\n\t1\nORDER BY\n\t1 ASC",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "SELECT $__timeGroup(g.date, '5s') as time,\n extract(epoch from end_date - start_date) as duration,\n\tavg(latitude) AS latitude,\n\tavg(longitude) AS longitude,\n max(p.charge_energy_added) AS energy_added\n FROM charging_processes p\n LEFT JOIN positions g ON g.id = p.position_id\n LEFT JOIN charges c ON c.charging_process_id = p.id\n WHERE g.date BETWEEN ($__timeFrom()::timestamp) AND ($__timeTo()::timestamp) AND p.car_id = $car_id \n GROUP BY 1,2\n HAVING NULLIF(mode() within group (order by charger_phases),0) is null --dc",
"refId": "B",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "charging_processes"
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "SELECT $__timeGroup(g.date, '5s') as time,\n extract(epoch from end_date - start_date) as duration,\n\tavg(latitude) AS latitude,\n\tavg(longitude) AS longitude,\n max(p.charge_energy_added) AS energy_added\n FROM charging_processes p\n LEFT JOIN positions g ON g.id = p.position_id\n LEFT JOIN charges c ON c.charging_process_id = p.id\n WHERE g.date BETWEEN ($__timeFrom()::timestamp) AND ($__timeTo()::timestamp) AND p.car_id = $car_id \n GROUP BY 1,2\n HAVING NULLIF(mode() within group (order by charger_phases),0) is NOT null --ac",
"refId": "C",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "charging_processes"
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "SELECT\n\t$__timeGroup(date, '5s') AS time,\n\tavg(latitude) AS latitude,\n\tavg(longitude) AS longitude\nFROM\n\tpositions\nWHERE\n car_id = $car_id AND\n\t$__timeFilter(date)\nGROUP BY\n\t1\nORDER BY\n\t1 ASC\nLIMIT 1",
"refId": "D",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "SELECT\n\t$__timeGroup(date, '5s') AS time,\n\tavg(latitude) AS latitude,\n\tavg(longitude) AS longitude\nFROM\n\tpositions\nWHERE\n car_id = $car_id AND\n\t$__timeFilter(date)\nGROUP BY\n\t1\nORDER BY\n\t1 DESC\nLIMIT 1",
"refId": "E",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "WITH all_events AS (\n SELECT $__timeGroup(d.end_date, '5s') as time,\n latitude,\n longitude,\n start_date, end_date,\n 'drive' as type\n FROM drives d\n LEFT JOIN positions g ON g.id = d.end_position_id\n WHERE d.end_date BETWEEN ($__timeFrom()::timestamp) AND ($__timeTo()::timestamp) AND d.car_id = $car_id\n UNION ALL\n SELECT $__timeGroup(p.end_date, '5s') as time,\n\t latitude,\n\t longitude,\n start_date, end_date,\n 'charging' as type\n FROM charging_processes p\n LEFT JOIN positions g ON g.id = p.position_id\n WHERE p.end_date BETWEEN ($__timeFrom()::timestamp) AND ($__timeTo()::timestamp) AND p.car_id = $car_id\n)\nSELECT time, latitude, longitude, duration, after, before FROM (\n SELECT *,\n ROW_NUMBER() OVER (ORDER BY time DESC) AS rn,\n extract(epoch from LEAD(start_date) OVER (ORDER BY start_date) - end_date) as duration,\n type as after,\n LEAD(type) OVER (ORDER BY start_date) as before\n FROM all_events\n) r\nWHERE rn > 1",
"refId": "parking",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"hide": false,
"rawQuery": true,
"rawSql": "WITH all_events AS (\n SELECT $__timeGroup(d.end_date, '5s') as time,\n latitude,\n longitude,\n start_date, end_date,\n 'drive' as type\n FROM drives d\n LEFT JOIN positions g ON g.id = d.end_position_id\n WHERE d.end_date BETWEEN ($__timeFrom()::timestamp) AND ($__timeTo()::timestamp) AND d.car_id = $car_id\n UNION ALL\n SELECT $__timeGroup(p.end_date, '5s') as time,\n\t latitude,\n\t longitude,\n start_date, end_date,\n 'charging' as type\n FROM charging_processes p\n LEFT JOIN positions g ON g.id = p.position_id\n WHERE p.end_date BETWEEN ($__timeFrom()::timestamp) AND ($__timeTo()::timestamp) AND p.car_id = $car_id\n)\nSELECT time, latitude, longitude, duration, after, before FROM (\n SELECT *,\n ROW_NUMBER() OVER (ORDER BY time DESC) AS rn,\n extract(epoch from LEAD(start_date) OVER (ORDER BY start_date) - end_date) as duration,\n type as after,\n LEAD(type) OVER (ORDER BY start_date) as before\n FROM all_events\n) r\nWHERE rn > 1",
"refId": "parking-p",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"transparent": true,
"type": "geomap"
},
{
"datasource": "TeslaMate",
"fieldConfig": {
"defaults": {
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "#c7d0d9",
"value": null
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "distance_km"
},
"properties": [
{
"id": "unit",
"value": "km"
},
{
"id": "displayName",
"value": "Distance"
}
]
},
{
"matcher": {
"id": "byName",
"options": "distance_mi"
},
"properties": [
{
"id": "unit",
"value": "mi"
},
{
"id": "displayName",
"value": "Distance"
}
]
}
]
},
"gridPos": {
"h": 4,
"w": 3,
"x": 0,
"y": 11
},
"id": 10,
"maxDataPoints": 100,
"options": {
"colorMode": "value",
"fieldOptions": {
"calcs": [
"lastNotNull"
]
},
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"mean"
],
"fields": "",
"values": true
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "10.4.1",
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT convert_km((max(odometer) - min(odometer))::numeric, '$length_unit') as \"distance_$length_unit\"\nFROM positions\nWHERE car_id = $car_id AND $__timeFilter(date)\nORDER BY 1;",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"type": "stat"
},
{
"datasource": "TeslaMate",
"description": "",
"fieldConfig": {
"defaults": {
"displayName": "Cost",
"mappings": [
{
"options": {
"match": "null",
"result": {
"text": "N/A"
}
},
"type": "special"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "#c7d0d9",
"value": null
}
]
},
"unit": "zł"
},
"overrides": []
},
"gridPos": {
"h": 2,
"w": 4,
"x": 3,
"y": 11
},
"id": 22,
"maxDataPoints": 100,
"options": {
"colorMode": "value",
"fieldOptions": {
"calcs": [
"lastNotNull"
]
},
"graphMode": "none",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"mean"
],
"fields": "",
"values": true
},
"showPercentChange": false,
"textMode": "value_and_name",
"wideLayout": true
},
"pluginVersion": "10.4.1",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH d AS (\n\tSELECT\n\t\tc.car_id,\n\t\tlag(end_[[preferred_range]]_range_km) OVER (ORDER BY start_date) - start_[[preferred_range]]_range_km AS range_loss,\n\t\tp.odometer - lag(p.odometer) OVER (ORDER BY start_date) AS distance\n\tFROM charging_processes c\n\tLEFT JOIN positions p ON p.id = c.position_id \n\tWHERE\n\t end_date IS NOT NULL AND\n\t c.car_id = $car_id AND\n\t $__timeFilter(start_date)\n\tORDER BY start_date\n),\n\nrange_loss_between_charges AS (\n SELECT sum(range_loss) AS range_loss\n FROM d\n WHERE distance >= 0 AND range_loss >= 0\n GROUP BY car_id\n),\n\ncharge_dates AS (\n\tSELECT\n\t\tmin(start_date) as first_charge,\n\t\tmax(end_date) as last_charge\n\tFROM\n\t\tcharging_processes\n\tWHERE\n\t\tend_date IS NOT NULL\n\t\tAND car_id = $car_id\n\t\tAND $__timeFilter(start_date)\n),\n\nrange_loss_before_first_charge AS (\n\tSELECT\n\t\tmax([[preferred_range]]_battery_range_km) - min([[preferred_range]]_battery_range_km) AS range_loss\n\tFROM positions, charge_dates\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(date)\n\t\tAND ((select first_charge from charge_dates) is null OR date < (select first_charge from charge_dates))\n),\n\nrange_loss_after_last_charge AS (\n\tSELECT\n\t\tmax([[preferred_range]]_battery_range_km) - min([[preferred_range]]_battery_range_km) AS range_loss\n\tFROM positions, charge_dates\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(date)\n\t\tAND date > (select last_charge from charge_dates)\t\n),\n\ntotal_range_loss AS (\n\tSELECT range_loss FROM range_loss_between_charges\n\tUNION ALL\n\tSELECT range_loss FROM range_loss_before_first_charge\n\tUNION ALL\n\tSELECT range_loss FROM range_loss_after_last_charge\n), \nused AS (\n SELECT sum(range_loss * c.efficiency) AS value\n FROM total_range_loss\n LEFT JOIN cars c ON c.id = $car_id\n),\nlast_drive AS (\n\tSELECT MAX(start_date) as date_to\n\tFROM drives \n\tWHERE start_date <= $__timeTo()\n)\nSELECT sum(LEAST(value - (running_energy_added - charge_energy_added), charge_energy_added) / charge_energy_added * cost) as Cost\nFROM (\n SELECT cp.start_date, cp.cost, cp.charge_energy_added, date_to,\n SUM(cp.charge_energy_added) OVER (ORDER BY cp.start_date DESC) AS running_energy_added\n FROM charging_processes cp, last_drive\n WHERE cp.end_date < last_drive.date_to and charge_energy_added > 0\n) AS subquery,used\nWHERE running_energy_added - charge_energy_added < value",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"type": "stat"
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
}
},
"decimals": 1,
"mappings": [],
"unit": "clocks"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "charging (AC)"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "#73BF69",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "charging (DC)"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "orange",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "driving"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byFrameRefID",
"options": "C"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "super-light-blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 7,
"w": 4,
"x": 7,
"y": 11
},
"id": 38,
"maxDataPoints": 3,
"options": {
"displayLabels": [
"name",
"percent"
],
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true,
"values": [
"value"
]
},
"pieType": "pie",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "time_series",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT\n\tnow() AS time,\n\tsum(extract(epoch FROM LEAST(end_date, $__timeTo()) - GREATEST(start_date, $__timeFrom()))) as duration_sec,\n\t'driving' as metric\nFROM\n\tdrives\nWHERE\n\tdrives.car_id = $car_id\n\tAND ($__timeFilter(start_date) OR $__timeFilter(end_date));",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "time_series",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH charges_current AS (\n SELECT\n\t\tcp.id,\n \textract(epoch FROM LEAST(end_date, $__timeTo()) - GREATEST(start_date, $__timeFrom())) as duration_sec,\n\t\tCASE WHEN NULLIF(mode() within group (order by charger_phases),0) is null THEN 'charging (DC)'\n\t\t\t\t ELSE 'charging (AC)'\n\t\tEND AS current\n\tFROM charging_processes cp\n RIGHT JOIN charges ON cp.id = charges.charging_process_id\n WHERE\n\t cp.car_id = $car_id\n \tAND ($__timeFilter(start_date) OR $__timeFilter(end_date))\n GROUP BY 1,2\n),\n\ncharges_total AS (\n SELECT\n \tsum(duration_sec) AS duration_sec,\n \tcurrent AS metric\n FROM charges_current\n GROUP BY 2\n ORDER BY metric\n)\n\nSELECT\n\tnow() AS time,\n\tcoalesce(duration_sec, 0) as duration_sec,\n metric\nFROM\n\tcharges_total;",
"refId": "B",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "time_series",
"hide": false,
"rawQuery": true,
"rawSql": "WITH driving_time AS (\n SELECT\n\tsum(extract(epoch FROM LEAST(end_date, $__timeTo()) - GREATEST(start_date, $__timeFrom()))) as duration_sec\nFROM\n\tdrives\nWHERE\n\tdrives.car_id = $car_id\n AND ($__timeFilter(start_date) OR $__timeFilter(end_date))\n), \ncharging_time AS (\n SELECT\n \tsum(extract(epoch FROM LEAST(end_date, $__timeTo()) - GREATEST(start_date, $__timeFrom()))) as duration_sec\n\tFROM charging_processes cp\n WHERE\n\t cp.car_id = $car_id\n \tAND ($__timeFilter(start_date) OR $__timeFilter(end_date))\n)\nSELECT\n\tnow() AS time,\n\textract(epoch FROM ($__timeTo()::timestamp - $__timeFrom()::timestamp)) - d.duration_sec - c.duration_sec as duration_sec,\n 'idle' as metric\nFROM\n\tdriving_time d, charging_time c\nLIMIT 1",
"refId": "C",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"type": "piechart"
},
{
"datasource": "TeslaMate",
"description": "",
"fieldConfig": {
"defaults": {
"mappings": [
{
"options": {
"match": "null",
"result": {
"text": "N/A"
}
},
"type": "special"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "#c7d0d9",
"value": null
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "speed_km"
},
"properties": [
{
"id": "unit",
"value": "velocitykmh"
},
{
"id": "displayName",
"value": "excl. breaks"
}
]
},
{
"matcher": {
"id": "byName",
"options": "speed_mi"
},
"properties": [
{
"id": "unit",
"value": "velocitymph"
},
{
"id": "displayName",
"value": "excl. breaks"
}
]
}
]
},
"gridPos": {
"h": 2,
"w": 2,
"x": 11,
"y": 11
},
"id": 26,
"maxDataPoints": 100,
"options": {
"colorMode": "value",
"fieldOptions": {
"calcs": [
"lastNotNull"
]
},
"graphMode": "none",
"justifyMode": "auto",
"orientation": "horizontal",
"reduceOptions": {
"calcs": [
"mean"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "value_and_name",
"wideLayout": true
},
"pluginVersion": "10.4.1",
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT\n convert_km(sum(end_position.odometer - start_position.odometer)::numeric, '$length_unit') / (sum(extract(epoch FROM end_position.date - start_position.date)) / 3600) as \"speed_$length_unit\"\nFROM\n\tdrives\n\tJOIN positions start_position ON start_position_id = start_position.id\n\tJOIN positions end_position ON end_position_id = end_position.id\nWHERE\n\tdrives.car_id = $car_id\n\tAND $__timeFilter(start_date)",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"type": "stat"
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"description": "",
"fieldConfig": {
"defaults": {
"displayName": "Cost [per 100km]",
"mappings": [
{
"options": {
"match": "null",
"result": {
"text": "N/A"
}
},
"type": "special"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "#c7d0d9",
"value": null
}
]
},
"unit": "zł"
},
"overrides": []
},
"gridPos": {
"h": 2,
"w": 4,
"x": 3,
"y": 13
},
"id": 43,
"maxDataPoints": 100,
"options": {
"colorMode": "value",
"fieldOptions": {
"calcs": [
"lastNotNull"
]
},
"graphMode": "none",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"mean"
],
"fields": "",
"values": true
},
"showPercentChange": false,
"textMode": "value_and_name",
"wideLayout": true
},
"pluginVersion": "10.4.1",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH d AS (\n\tSELECT\n\t\tc.car_id,\n\t\tlag(end_[[preferred_range]]_range_km) OVER (ORDER BY start_date) - start_[[preferred_range]]_range_km AS range_loss,\n\t\tp.odometer - lag(p.odometer) OVER (ORDER BY start_date) AS distance\n\tFROM charging_processes c\n\tLEFT JOIN positions p ON p.id = c.position_id \n\tWHERE\n\t end_date IS NOT NULL AND\n\t c.car_id = $car_id AND\n\t $__timeFilter(start_date)\n\tORDER BY start_date\n),\n\nrange_loss_between_charges AS (\n SELECT sum(range_loss) AS range_loss\n FROM d\n WHERE distance >= 0 AND range_loss >= 0\n GROUP BY car_id\n),\n\ncharge_dates AS (\n\tSELECT\n\t\tmin(start_date) as first_charge,\n\t\tmax(end_date) as last_charge\n\tFROM\n\t\tcharging_processes\n\tWHERE\n\t\tend_date IS NOT NULL\n\t\tAND car_id = $car_id\n\t\tAND $__timeFilter(start_date)\n),\n\nrange_loss_before_first_charge AS (\n\tSELECT\n\t\tmax([[preferred_range]]_battery_range_km) - min([[preferred_range]]_battery_range_km) AS range_loss\n\tFROM positions, charge_dates\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(date)\n\t\tAND ((select first_charge from charge_dates) is null OR date < (select first_charge from charge_dates))\n),\n\nrange_loss_after_last_charge AS (\n\tSELECT\n\t\tmax([[preferred_range]]_battery_range_km) - min([[preferred_range]]_battery_range_km) AS range_loss\n\tFROM positions, charge_dates\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(date)\n\t\tAND date > (select last_charge from charge_dates)\t\n),\n\ntotal_range_loss AS (\n\tSELECT range_loss FROM range_loss_between_charges\n\tUNION ALL\n\tSELECT range_loss FROM range_loss_before_first_charge\n\tUNION ALL\n\tSELECT range_loss FROM range_loss_after_last_charge\n), \nused AS (\n SELECT sum(range_loss * c.efficiency) AS value\n FROM total_range_loss\n LEFT JOIN cars c ON c.id = $car_id\n),\nlast_drive AS (\n\tSELECT MAX(start_date) as date_to\n\tFROM drives \n\tWHERE start_date <= $__timeTo()\n)\nSELECT sum(LEAST(value - (running_energy_added - charge_energy_added), charge_energy_added) / charge_energy_added * cost) / (\n SELECT convert_km((max(odometer) - min(odometer))::numeric, '$length_unit') as \"distance_$length_unit\"\n FROM positions\n WHERE car_id = $car_id AND $__timeFilter(date)\n) * 100 as \"Cost per 100km\" \nFROM (\n SELECT cp.start_date, cp.cost, cp.charge_energy_added, date_to,\n SUM(cp.charge_energy_added) OVER (ORDER BY cp.start_date DESC) AS running_energy_added\n FROM charging_processes cp, last_drive\n WHERE cp.end_date < last_drive.date_to and charge_energy_added > 0\n) AS subquery,used\nWHERE running_energy_added - charge_energy_added < value",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"type": "stat"
},
{
"datasource": "TeslaMate",
"fieldConfig": {
"defaults": {
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "#c7d0d9",
"value": null
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "speed_km"
},
"properties": [
{
"id": "unit",
"value": "velocitykmh"
},
{
"id": "displayName",
"value": "incl. DC charging"
}
]
},
{
"matcher": {
"id": "byName",
"options": "speed_mi"
},
"properties": [
{
"id": "unit",
"value": "velocitymph"
},
{
"id": "displayName",
"value": "incl. DC charging"
}
]
}
]
},
"gridPos": {
"h": 2,
"w": 2,
"x": 11,
"y": 13
},
"id": 28,
"maxDataPoints": 100,
"options": {
"colorMode": "value",
"fieldOptions": {
"calcs": [
"lastNotNull"
]
},
"graphMode": "none",
"justifyMode": "auto",
"orientation": "horizontal",
"reduceOptions": {
"calcs": [
"mean"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "value_and_name",
"wideLayout": true
},
"pluginVersion": "10.4.1",
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH dc_charges AS (\n SELECT\n\t\tcp.id,\n extract(epoch FROM cp.end_date - cp.start_date) as duration_sec,\n\t\tCASE WHEN NULLIF(mode() within group (order by charger_phases),0) is null THEN 'DC'\n\t\t\t\t ELSE 'AC'\n\t\tEND AS current\n\tFROM charging_processes cp\n RIGHT JOIN charges ON cp.id = charges.charging_process_id\n WHERE\n\t cp.car_id = $car_id\n\t AND cp.charge_energy_added > 0\n AND ($__timeFilter(start_date) OR $__timeFilter(end_date))\n GROUP BY 1,2\n),\n\ndata AS (\n (\n SELECT\n sum(end_position.odometer - start_position.odometer) as distance, \n sum(extract(epoch FROM end_position.date - start_position.date)) as duration_sec\n FROM\n drives\n JOIN positions start_position ON start_position_id = start_position.id\n JOIN positions end_position ON end_position_id = end_position.id\n WHERE\n drives.car_id = $car_id\n AND $__timeFilter(start_date)\n ) UNION ALL (\n SELECT\n NULL as distance,\n sum(duration_sec)\n FROM\n dc_charges\n WHERE\n current = 'DC'\n )\n)\n\nSELECT convert_km(sum(distance)::numeric, '$length_unit') / (sum(duration_sec) / 3600) as \"speed_$length_unit\"\nfrom data",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"type": "stat"
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"fieldConfig": {
"defaults": {
"decimals": 1,
"displayName": "${__cell_0}",
"links": [],
"mappings": [],
"min": 0,
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "blue",
"value": null
}
]
},
"unit": "kwatth"
},
"overrides": [
{
"matcher": {
"id": "byFrameRefID",
"options": "B"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "orange",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byFrameRefID",
"options": "C"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "green",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 3,
"w": 5,
"x": 0,
"y": 15
},
"id": 40,
"options": {
"displayMode": "lcd",
"maxVizHeight": 300,
"minVizHeight": 10,
"minVizWidth": 0,
"namePlacement": "top",
"orientation": "horizontal",
"reduceOptions": {
"calcs": [
"mean"
],
"fields": "",
"values": true
},
"showUnfilled": false,
"sizing": "auto",
"valueMode": "color"
},
"pluginVersion": "10.4.1",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH d AS (\n\tSELECT\n\t\tc.car_id,\n\t\tlag(end_[[preferred_range]]_range_km) OVER (ORDER BY start_date) - start_[[preferred_range]]_range_km AS range_loss,\n\t\tp.odometer - lag(p.odometer) OVER (ORDER BY start_date) AS distance\n\tFROM charging_processes c\n\tLEFT JOIN positions p ON p.id = c.position_id \n\tWHERE\n\t end_date IS NOT NULL AND\n\t c.car_id = $car_id AND\n\t $__timeFilter(start_date)\n\tORDER BY start_date\n),\n\nrange_loss_between_charges AS (\n SELECT sum(range_loss) AS range_loss\n FROM d\n WHERE distance >= 0 AND range_loss >= 0\n GROUP BY car_id\n),\n\ncharge_dates AS (\n\tSELECT\n\t\tmin(start_date) as first_charge,\n\t\tmax(end_date) as last_charge\n\tFROM\n\t\tcharging_processes\n\tWHERE\n\t\tend_date IS NOT NULL\n\t\tAND car_id = $car_id\n\t\tAND $__timeFilter(start_date)\n),\n\nrange_loss_before_first_charge AS (\n\tSELECT\n\t\tmax([[preferred_range]]_battery_range_km) - min([[preferred_range]]_battery_range_km) AS range_loss\n\tFROM positions, charge_dates\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(date)\n\t\tAND ((select first_charge from charge_dates) is null OR date < (select first_charge from charge_dates))\n),\n\nrange_loss_after_last_charge AS (\n\tSELECT\n\t\tmax([[preferred_range]]_battery_range_km) - min([[preferred_range]]_battery_range_km) AS range_loss\n\tFROM positions, charge_dates\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(date)\n\t\tAND date > (select last_charge from charge_dates)\t\n),\n\ntotal_range_loss AS (\n\tSELECT range_loss FROM range_loss_between_charges\n\tUNION ALL\n\tSELECT range_loss FROM range_loss_before_first_charge\n\tUNION ALL\n\tSELECT range_loss FROM range_loss_after_last_charge\n)\n\nSELECT 'used' as metric, sum(range_loss * c.efficiency) AS value\nFROM total_range_loss\nLEFT JOIN cars c ON c.id = $car_id;",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"group": [],
"hide": false,
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH charges_current AS (\n SELECT\n\t\tcp.id,\n\t\tcp.charge_energy_added as energy_added,\n\t\tCASE WHEN NULLIF(mode() within group (order by charger_phases),0) is null THEN 'added (DC)'\n\t\t\t\t ELSE 'added (AC)'\n\t\tEND AS metric\n\tFROM charging_processes cp\n RIGHT JOIN charges ON cp.id = charges.charging_process_id\n WHERE\n\t cp.car_id = $car_id\n\t AND cp.charge_energy_added > 0\n \tAND ($__timeFilter(start_date) OR $__timeFilter(end_date))\n GROUP BY 1,2\n)\n\nSELECT metric, sum(energy_added) AS energy_added\nFROM charges_current\nWHERE metric = 'added (DC)'\nGROUP BY 1\nORDER BY 1 DESC;",
"refId": "B",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"editorMode": "code",
"format": "table",
"group": [],
"hide": false,
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH charges_current AS (\n SELECT\n\t\tcp.id,\n\t\tcp.charge_energy_added as energy_added,\n\t\tCASE WHEN NULLIF(mode() within group (order by charger_phases),0) is null THEN 'added (DC)'\n\t\t\t\t ELSE 'added (AC)'\n\t\tEND AS metric\n\tFROM charging_processes cp\n RIGHT JOIN charges ON cp.id = charges.charging_process_id\n WHERE\n\t cp.car_id = $car_id\n\t AND cp.charge_energy_added > 0\n \tAND ($__timeFilter(start_date) OR $__timeFilter(end_date))\n GROUP BY 1,2\n)\n\nSELECT metric, sum(energy_added) AS energy_added\nFROM charges_current\nWHERE metric = 'added (AC)'\nGROUP BY 1\nORDER BY 1 DESC;",
"refId": "C",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"type": "bargauge"
},
{
"datasource": "TeslaMate",
"description": "",
"fieldConfig": {
"defaults": {
"decimals": 0,
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "#c7d0d9",
"value": null
}
]
},
"unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "consumption_km"
},
"properties": [
{
"id": "unit",
"value": "Wh/km"
},
{
"id": "displayName",
"value": "gross"
}
]
},
{
"matcher": {
"id": "byName",
"options": "consumption_mi"
},
"properties": [
{
"id": "unit",
"value": "Wh/mi"
},
{
"id": "displayName",
"value": "gross"
}
]
}
]
},
"gridPos": {
"h": 3,
"w": 2,
"x": 5,
"y": 15
},
"id": 32,
"maxDataPoints": 100,
"options": {
"colorMode": "value",
"fieldOptions": {
"calcs": [
"lastNotNull"
]
},
"graphMode": "none",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"mean"
],
"fields": "",
"values": true
},
"showPercentChange": false,
"textMode": "value_and_name",
"wideLayout": false
},
"pluginVersion": "10.4.1",
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH d AS (\n\tSELECT\n\t\tc.car_id,\n\t\tlag(end_[[preferred_range]]_range_km) OVER (ORDER BY start_date) - start_[[preferred_range]]_range_km AS range_loss,\n\t\tp.odometer - lag(p.odometer) OVER (ORDER BY start_date) AS distance\n\tFROM charging_processes c\n\tLEFT JOIN positions p ON p.id = c.position_id \n\tWHERE\n\t end_date IS NOT NULL AND\n\t c.car_id = $car_id AND\n\t $__timeFilter(start_date)\n\tORDER BY start_date\n),\n\nrange_loss_between_charges AS (\n SELECT sum(range_loss) AS range_loss\n FROM d\n WHERE distance >= 0 AND range_loss >= 0\n GROUP BY car_id\n),\n\ncharge_dates AS (\n\tSELECT\n\t\tmin(start_date) as first_charge,\n\t\tmax(end_date) as last_charge\n\tFROM\n\t\tcharging_processes\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(start_date)\n),\n\nrange_loss_before_first_charge AS (\n\tSELECT\n\t\tmax([[preferred_range]]_battery_range_km) - min([[preferred_range]]_battery_range_km) AS range_loss\n\tFROM positions, charge_dates\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(date)\n\t\tAND ((select first_charge from charge_dates) is null OR date < (select first_charge from charge_dates))\n),\n\nrange_loss_after_last_charge AS (\n\tSELECT\n\t\tmax([[preferred_range]]_battery_range_km) - min([[preferred_range]]_battery_range_km) AS range_loss\n\tFROM positions, charge_dates\n\tWHERE\n\t\tcar_id = $car_id\n\t\tAND $__timeFilter(date)\n\t\tAND date > (select last_charge from charge_dates)\t\n),\n\ntotal_range_loss AS (\n SELECT sum(range_loss) as range_loss\n FROM (\n SELECT range_loss FROM range_loss_between_charges\n UNION ALL\n SELECT range_loss FROM range_loss_before_first_charge\n UNION ALL\n SELECT range_loss FROM range_loss_after_last_charge\n ) r\n),\n\ndistance AS (\n SELECT max(odometer) - min(odometer) as distance\n FROM positions\n WHERE car_id = $car_id AND $__timeFilter(date)\n)\n\nSELECT \n NULLIF(range_loss, 0) * (c.efficiency * 1000) / convert_km(NULLIF(distance::numeric, 0), '$length_unit') as \"consumption_$length_unit\"\nFROM total_range_loss, distance\nLEFT JOIN cars c ON c.id = $car_id",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"type": "stat"
},
{
"datasource": "TeslaMate",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "hidden",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 100,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"insertNulls": false,
"lineInterpolation": "linear",
"lineStyle": {
"fill": "solid"
},
"lineWidth": 0,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "never",
"spanNulls": true,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "short"
},
"overrides": [
{
"matcher": {
"id": "byRegexp",
"options": ".*_m$"
},
"properties": [
{
"id": "unit",
"value": "lengthm"
}
]
},
{
"matcher": {
"id": "byRegexp",
"options": ".*_ft$"
},
"properties": [
{
"id": "unit",
"value": "lengthft"
}
]
},
{
"matcher": {
"id": "byRegexp",
"options": "elevation_.*"
},
"properties": [
{
"id": "displayName",
"value": "Elevation"
},
{
"id": "color",
"value": {
"fixedColor": "red",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 3,
"w": 2,
"x": 11,
"y": 15
},
"id": 8,
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "multi",
"sort": "none"
}
},
"pluginVersion": "8.5.4",
"targets": [
{
"datasource": "TeslaMate",
"format": "time_series",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT\n\t$__timeGroup(date, '5s'),\n\tROUND(convert_m(avg(elevation), '$alternative_length_unit')) AS elevation_[[alternative_length_unit]]\nFROM\n\tpositions\nWHERE\n car_id = $car_id AND\n date BETWEEN ($__timeFrom()::timestamp - interval '1 day') AND ($__timeTo()::timestamp + interval '1 day')\nGROUP BY\n 1\nORDER BY\n 1 ASC",
"refId": "A",
"select": [
[
{
"params": [
"latitude"
],
"type": "column"
}
]
],
"table": "addresses",
"timeColumn": "inserted_at",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"type": "timeseries"
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"align": "auto",
"cellOptions": {
"type": "auto"
},
"inspect": false
},
"decimals": 2,
"displayName": "",
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "short"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "start_date"
},
"properties": [
{
"id": "displayName",
"value": "Date"
},
{
"id": "unit",
"value": "dateTimeAsLocal"
},
{
"id": "links",
"value": [
{
"targetBlank": false,
"title": "View charge details",
"url": "d/BHhxFeZRz?from=${__data.fields.start_date_ts.numeric}&to=${__data.fields.end_date_ts.numeric}&var-car_id=${__data.fields.car_id.numeric}&var-charging_process_id=${__data.fields.id.numeric:raw}"
}
]
},
{
"id": "custom.align"
},
{
"id": "custom.width",
"value": 180
}
]
},
{
"matcher": {
"id": "byName",
"options": "charge_energy_added"
},
"properties": [
{
"id": "displayName",
"value": "Added"
},
{
"id": "unit",
"value": "kwatth"
},
{
"id": "decimals",
"value": 2
},
{
"id": "custom.align"
},
{
"id": "custom.width",
"value": 100
}
]
},
{
"matcher": {
"id": "byName",
"options": "start_battery_level"
},
"properties": [
{
"id": "displayName",
"value": "% Start"
},
{
"id": "unit",
"value": "percent"
},
{
"id": "custom.align"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 65
}
]
},
{
"matcher": {
"id": "byName",
"options": "end_battery_level"
},
"properties": [
{
"id": "displayName",
"value": "% End"
},
{
"id": "unit",
"value": "percent"
},
{
"id": "custom.align"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 65
}
]
},
{
"matcher": {
"id": "byName",
"options": "duration_min"
},
"properties": [
{
"id": "displayName",
"value": "Duration"
},
{
"id": "unit",
"value": "clocks"
},
{
"id": "decimals",
"value": 1
},
{
"id": "custom.align"
},
{
"id": "custom.width",
"value": 100
}
]
},
{
"matcher": {
"id": "byName",
"options": "cost"
},
"properties": [
{
"id": "displayName",
"value": "Cost"
},
{
"id": "unit",
"value": "none"
},
{
"id": "decimals",
"value": 2
},
{
"id": "links",
"value": [
{
"targetBlank": false,
"title": "Set Cost",
"url": "[[base_url:raw]]/charge-cost/${__data.fields.id.numeric:raw}"
}
]
},
{
"id": "custom.align"
},
{
"id": "custom.width",
"value": 80
}
]
},
{
"matcher": {
"id": "byRegexp",
"options": "/.*_ts/"
},
"properties": [
{
"id": "unit",
"value": "short"
},
{
"id": "decimals",
"value": 2
},
{
"id": "custom.align"
},
{
"id": "custom.hidden",
"value": true
}
]
},
{
"matcher": {
"id": "byName",
"options": "id"
},
"properties": [
{
"id": "custom.align"
},
{
"id": "custom.hidden",
"value": true
}
]
},
{
"matcher": {
"id": "byName",
"options": "address"
},
"properties": [
{
"id": "displayName",
"value": "Location"
},
{
"id": "links",
"value": [
{
"targetBlank": true,
"title": "Create or edit geo-fence",
"url": "[[base_url:raw]]/geo-fences/${__data.fields.path}"
}
]
},
{
"id": "custom.align"
},
{
"id": "custom.minWidth",
"value": 200
}
]
},
{
"matcher": {
"id": "byName",
"options": "distance_km"
},
"properties": [
{
"id": "displayName",
"value": "Driven"
},
{
"id": "unit",
"value": "lengthkm"
},
{
"id": "custom.align"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 80
}
]
},
{
"matcher": {
"id": "byName",
"options": "charge_energy_added_per_hour"
},
"properties": [
{
"id": "displayName",
"value": "kW"
},
{
"id": "unit",
"value": "kwatt"
},
{
"id": "decimals",
"value": 1
},
{
"id": "custom.cellOptions",
"value": {
"type": "color-text"
}
},
{
"id": "custom.align"
},
{
"id": "thresholds",
"value": {
"mode": "absolute",
"steps": [
{
"color": "#96D98D",
"value": null
},
{
"color": "#56A64B",
"value": 20
},
{
"color": "#37872D",
"value": 55
}
]
}
},
{
"id": "custom.width",
"value": 80
}
]
},
{
"matcher": {
"id": "byName",
"options": "distance_mi"
},
"properties": [
{
"id": "displayName",
"value": "Driven"
},
{
"id": "unit",
"value": "lengthmi"
},
{
"id": "custom.align"
},
{
"id": "decimals",
"value": 0
}
]
},
{
"matcher": {
"id": "byName",
"options": "path"
},
"properties": [
{
"id": "custom.align"
},
{
"id": "custom.hidden",
"value": true
}
]
},
{
"matcher": {
"id": "byName",
"options": "charge_energy_used"
},
"properties": [
{
"id": "displayName",
"value": "Used"
},
{
"id": "unit",
"value": "kwatth"
},
{
"id": "decimals",
"value": 2
},
{
"id": "custom.align"
},
{
"id": "custom.width",
"value": 100
}
]
},
{
"matcher": {
"id": "byName",
"options": "car_id"
},
"properties": [
{
"id": "custom.align"
},
{
"id": "custom.hidden",
"value": true
}
]
},
{
"matcher": {
"id": "byName",
"options": "Date"
},
"properties": [
{
"id": "custom.width",
"value": 185
}
]
},
{
"matcher": {
"id": "byName",
"options": "Duration"
},
"properties": [
{
"id": "custom.width",
"value": 78
}
]
},
{
"matcher": {
"id": "byName",
"options": "Added"
},
"properties": [
{
"id": "custom.width",
"value": 94
}
]
},
{
"matcher": {
"id": "byName",
"options": "Used"
},
"properties": [
{
"id": "custom.width",
"value": 93
}
]
},
{
"matcher": {
"id": "byName",
"options": "Location"
},
"properties": [
{
"id": "custom.width",
"value": 215
}
]
},
{
"matcher": {
"id": "byName",
"options": "Cost"
},
"properties": [
{
"id": "custom.width",
"value": 71
}
]
},
{
"matcher": {
"id": "byName",
"options": "% Start"
},
"properties": [
{
"id": "custom.width",
"value": 72
}
]
},
{
"matcher": {
"id": "byName",
"options": "% End"
},
"properties": [
{
"id": "custom.width",
"value": 62
}
]
}
]
},
"gridPos": {
"h": 10,
"w": 13,
"x": 0,
"y": 18
},
"id": 36,
"options": {
"cellHeight": "sm",
"footer": {
"countRows": false,
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true,
"sortBy": [
{
"desc": false,
"displayName": "Date"
}
]
},
"pluginVersion": "10.4.1",
"targets": [
{
"datasource": "TeslaMate",
"editorMode": "code",
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH data AS (\n SELECT\n (round(extract(epoch FROM start_date) - 10) * 1000) AS start_date_ts,\n (round(extract(epoch FROM end_date) + 10) * 1000) AS end_date_ts,\n start_date,\n end_date,\n CONCAT_WS(', ', addresses.city, COALESCE(addresses.name, CONCAT_WS(' ', addresses.road, addresses.house_number))) AS address,\n g.name as geofence_name,\n g.id as geofence_id,\n p.latitude,\n p.longitude,\n charge_energy_added,\n charge_energy_used,\n duration_min,\n start_battery_level,\n end_battery_level,\n start_[[preferred_range]]_range_km,\n end_[[preferred_range]]_range_km,\n outside_temp_avg,\n c.id,\n lag(end_[[preferred_range]]_range_km) OVER (ORDER BY start_date) - start_[[preferred_range]]_range_km AS range_loss,\n p.odometer - lag(p.odometer) OVER (ORDER BY start_date) AS distance,\n cars.efficiency,\n c.car_id,\n cost\n FROM\n charging_processes c\n LEFT JOIN positions p ON p.id = c.position_id\n LEFT JOIN cars ON cars.id = c.car_id\n LEFT JOIN addresses ON addresses.id = c.address_id\n LEFT JOIN geofences g ON g.id = geofence_id\nWHERE \n (charge_energy_added IS NULL OR charge_energy_added > 0) AND\n c.car_id = $car_id AND\n ($__timeFilter(start_date) or $__timeFilter(end_date))\nORDER BY\n start_date\n)\nSELECT\n start_date_ts,\n end_date_ts,\n CASE WHEN geofence_id IS NULL THEN CONCAT('new?lat=', latitude, '&lng=', longitude)\n WHEN geofence_id IS NOT NULL THEN CONCAT(geofence_id, '/edit')\n END as path,\n car_id,\n id,\n -- Columns\n start_date,\n COALESCE(geofence_name, address) as address, \n duration_min * 60 as duration_min,\n charge_energy_added * 60 / NULLIF (duration_min, 0) AS charge_energy_added_per_hour,\n cost,\n charge_energy_added,\n charge_energy_used,\n start_battery_level,\n end_battery_level--,\n --convert_km(distance::numeric, '$length_unit') AS distance_$length_unit\n FROM\n data\nWHERE\n (distance >= 0 OR distance IS NULL)\nORDER BY\n start_date ASC;\n ",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Charges",
"transformations": [
{
"id": "merge",
"options": {
"reducers": []
}
}
],
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"align": "auto",
"cellOptions": {
"type": "auto"
},
"inspect": false
},
"decimals": 2,
"displayName": "",
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "short"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "start_date"
},
"properties": [
{
"id": "displayName",
"value": "Date"
},
{
"id": "unit",
"value": "dateTimeAsLocal"
},
{
"id": "links",
"value": [
{
"targetBlank": false,
"title": "View drive details",
"url": "d/zm7wN6Zgz?from=${__data.fields.start_date_ts.numeric}&to=${__data.fields.end_date_ts.numeric}&var-car_id=${__data.fields.car_id.numeric}&var-drive_id=${__data.fields.drive_id.numeric}"
}
]
},
{
"id": "custom.width",
"value": 180
}
]
},
{
"matcher": {
"id": "byName",
"options": "consumption_kwh_km"
},
"properties": [
{
"id": "displayName",
"value": "Consumption"
},
{
"id": "unit",
"value": "Wh/km"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 100
}
]
},
{
"matcher": {
"id": "byName",
"options": "consumption_kwh_mi"
},
"properties": [
{
"id": "displayName",
"value": "Consumption"
},
{
"id": "unit",
"value": "Wh/mi"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 100
}
]
},
{
"matcher": {
"id": "byName",
"options": "distance_km"
},
"properties": [
{
"id": "displayName",
"value": "km"
},
{
"id": "unit",
"value": "lengthkm"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 80
}
]
},
{
"matcher": {
"id": "byName",
"options": "start_address"
},
"properties": [
{
"id": "displayName",
"value": "Start"
},
{
"id": "links",
"value": [
{
"targetBlank": true,
"title": "Create or edit geo-fence",
"url": "[[base_url:raw]]/geo-fences/${__data.fields.start_path}"
}
]
},
{
"id": "custom.minWidth",
"value": 200
}
]
},
{
"matcher": {
"id": "byName",
"options": "end_address"
},
"properties": [
{
"id": "displayName",
"value": "Destination"
},
{
"id": "links",
"value": [
{
"targetBlank": true,
"title": "Create or edit geo-fence",
"url": "[[base_url:raw]]/geo-fences/${__data.fields.end_path}"
}
]
},
{
"id": "custom.minWidth",
"value": 200
}
]
},
{
"matcher": {
"id": "byName",
"options": "duration_min"
},
"properties": [
{
"id": "displayName",
"value": "Duration"
},
{
"id": "unit",
"value": "clocks"
},
{
"id": "decimals",
"value": 1
},
{
"id": "links",
"value": []
},
{
"id": "custom.width",
"value": 100
}
]
},
{
"matcher": {
"id": "byRegexp",
"options": "/.*_ts/"
},
"properties": [
{
"id": "custom.hidden",
"value": true
}
]
},
{
"matcher": {
"id": "byName",
"options": "distance_mi"
},
"properties": [
{
"id": "displayName",
"value": "mi"
},
{
"id": "unit",
"value": "lengthmi"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 80
}
]
},
{
"matcher": {
"id": "byName",
"options": "% Start"
},
"properties": [
{
"id": "unit",
"value": "percent"
},
{
"id": "custom.align"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 70
}
]
},
{
"matcher": {
"id": "byName",
"options": "% End"
},
"properties": [
{
"id": "unit",
"value": "percent"
},
{
"id": "custom.align"
},
{
"id": "decimals",
"value": 0
},
{
"id": "custom.width",
"value": 63
}
]
},
{
"matcher": {
"id": "byRegexp",
"options": "(start_path|end_path|duration_str|car_id|drive_id)"
},
"properties": [
{
"id": "custom.hidden",
"value": true
}
]
},
{
"matcher": {
"id": "byName",
"options": "Speed"
},
"properties": [
{
"id": "unit",
"value": "velocitykmh"
},
{
"id": "decimals"
},
{
"id": "custom.width",
"value": 91
},
{
"id": "displayName",
"value": "Avg Speed"
}
]
},
{
"matcher": {
"id": "byName",
"options": "Duration"
},
"properties": [
{
"id": "custom.width",
"value": 88
}
]
},
{
"matcher": {
"id": "byName",
"options": "Date"
},
"properties": [
{
"id": "custom.width",
"value": 187
}
]
},
{
"matcher": {
"id": "byName",
"options": "Consumption"
},
"properties": [
{
"id": "custom.width",
"value": 108
}
]
},
{
"matcher": {
"id": "byName",
"options": "Avg Speed"
},
"properties": [
{
"id": "custom.width",
"value": 92
}
]
},
{
"matcher": {
"id": "byName",
"options": "outside_temp_c"
},
"properties": [
{
"id": "unit",
"value": "celsius"
},
{
"id": "decimals",
"value": 1
},
{
"id": "displayName",
"value": "Temp"
}
]
},
{
"matcher": {
"id": "byName",
"options": "Temp"
},
"properties": [
{
"id": "custom.width",
"value": 94
}
]
}
]
},
"gridPos": {
"h": 10,
"w": 11,
"x": 13,
"y": 18
},
"id": 2,
"options": {
"cellHeight": "sm",
"footer": {
"countRows": false,
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true,
"sortBy": [
{
"desc": false,
"displayName": "Date"
}
]
},
"pluginVersion": "10.4.1",
"targets": [
{
"alias": "",
"datasource": "TeslaMate",
"editorMode": "code",
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "WITH data AS (\n SELECT\n round(extract(epoch FROM start_date)) * 1000 AS start_date_ts,\n round(extract(epoch FROM end_date)) * 1000 AS end_date_ts,\n car.id as car_id,\n CASE WHEN start_geofence.id IS NULL THEN CONCAT('new?lat=', start_position.latitude, '&lng=', start_position.longitude)\n WHEN start_geofence.id IS NOT NULL THEN CONCAT(start_geofence.id, '/edit')\n END as start_path,\n CASE WHEN end_geofence.id IS NULL THEN CONCAT('new?lat=', end_position.latitude, '&lng=', end_position.longitude)\n WHEN end_geofence.id IS NOT NULL THEN CONCAT(end_geofence.id, '/edit')\n END as end_path,\n TO_CHAR((duration_min * INTERVAL '1 minute'), 'HH24:MI') as duration_str,\n drives.id as drive_id,\n -- Columns\n start_date,\n 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,\n 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,\n duration_min,\n distance,\n start_position.usable_battery_level as start_usable_battery_level,\n start_position.battery_level as start_battery_level,\n end_position.usable_battery_level as end_usable_battery_level,\n end_position.battery_level as end_battery_level,\n start_position.battery_level != start_position.usable_battery_level OR end_position.battery_level != end_position.usable_battery_level as reduced_range,\n duration_min > 1 AND distance > 1 AND ( \n start_position.usable_battery_level IS NULL OR end_position.usable_battery_level IS NULL\tOR\n (end_position.battery_level - end_position.usable_battery_level) = 0 \n ) as is_sufficiently_precise,\n NULLIF(GREATEST(start_[[preferred_range]]_range_km - end_[[preferred_range]]_range_km, 0), 0) as range_diff,\n car.efficiency as car_efficiency,\n outside_temp_avg,\n distance / NULLIF(duration_min, 0) * 60 AS avg_speed\n FROM drives\n LEFT JOIN addresses start_address ON start_address_id = start_address.id\n LEFT JOIN addresses end_address ON end_address_id = end_address.id\n LEFT JOIN positions start_position ON start_position_id = start_position.id\n LEFT JOIN positions end_position ON end_position_id = end_position.id\n LEFT JOIN geofences start_geofence ON start_geofence_id = start_geofence.id\n LEFT JOIN geofences end_geofence ON end_geofence_id = end_geofence.id\n LEFT JOIN cars car ON car.id = drives.car_id\n WHERE ($__timeFilter(start_date) OR $__timeFilter(end_date)) AND drives.car_id = $car_id\n ORDER BY start_date DESC\n)\nSELECT\n start_date_ts,\n end_date_ts,\n car_id,\n start_path,\n end_path,\n duration_str,\n drive_id,\n -- Columns\n start_date,\n --start_address,\n --end_address,\n duration_min*60 as duration_min,\n convert_km(distance::numeric, '$length_unit') AS distance_$length_unit,\n start_battery_level as \"% Start\",\n end_battery_level as \"% End\",\n convert_celsius(outside_temp_avg, 'C') as outside_temp_c,\n round(avg_speed) as \"Speed\",\n CASE WHEN is_sufficiently_precise THEN range_diff * car_efficiency / distance * 1000 * CASE WHEN '$length_unit' = 'km' THEN 1\n WHEN '$length_unit' = 'mi' THEN 1.60934\n END\n END AS consumption_kWh_$length_unit\nFROM data\n--where is_sufficiently_precise;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Drives",
"type": "table"
}
],
"schemaVersion": 39,
"tags": [],
"templating": {
"list": [
{
"current": {},
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"definition": "SELECT concat(case when length(name) > 0 then name||' - ' else '' end, model, ' ', marketing_name) AS __text, id AS __value FROM cars ORDER BY display_priority ASC, name ASC;",
"hide": 0,
"includeAll": false,
"label": "Car",
"multi": false,
"name": "car_id",
"options": [],
"query": "SELECT concat(case when length(name) > 0 then name||' - ' else '' end, model, ' ', marketing_name) AS __text, id AS __value FROM cars ORDER BY display_priority ASC, name ASC;",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tagsQuery": "",
"type": "query",
"useTags": false
},
{
"current": {},
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"definition": "select unit_of_temperature from settings limit 1;",
"hide": 2,
"includeAll": false,
"label": "temperature unit",
"multi": false,
"name": "temp_unit",
"options": [],
"query": "select unit_of_temperature from settings limit 1;",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tagsQuery": "",
"type": "query",
"useTags": false
},
{
"current": {},
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"definition": "select unit_of_length from settings limit 1;",
"hide": 2,
"includeAll": false,
"label": "",
"multi": false,
"name": "length_unit",
"options": [],
"query": "select unit_of_length from settings limit 1;",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tagsQuery": "",
"type": "query",
"useTags": false
},
{
"current": {},
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"definition": "select case when unit_of_length = 'km' then 'm' when unit_of_length = 'mi' then 'ft' end from settings limit 1;",
"hide": 2,
"includeAll": false,
"label": "",
"multi": false,
"name": "alternative_length_unit",
"options": [],
"query": "select case when unit_of_length = 'km' then 'm' when unit_of_length = 'mi' then 'ft' end from settings limit 1;",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tagsQuery": "",
"type": "query",
"useTags": false
},
{
"current": {},
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"definition": "select preferred_range from settings limit 1;",
"hide": 2,
"includeAll": false,
"multi": false,
"name": "preferred_range",
"options": [],
"query": "select preferred_range from settings limit 1;",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tagsQuery": "",
"type": "query",
"useTags": false
},
{
"current": {},
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"definition": "select base_url from settings limit 1;",
"hide": 2,
"includeAll": false,
"label": "",
"multi": false,
"name": "base_url",
"options": [],
"query": "select base_url from settings limit 1;",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tagsQuery": "",
"type": "query",
"useTags": false
},
{
"current": {},
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"definition": "select extract(epoch from min(start_date)) * 1000 from drives where $__timeFilter(end_date)",
"hide": 2,
"includeAll": false,
"multi": false,
"name": "from",
"options": [],
"query": "select extract(epoch from min(start_date)) * 1000 from drives where $__timeFilter(end_date)",
"refresh": 2,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"type": "query"
},
{
"current": {},
"datasource": {
"type": "postgres",
"uid": "TeslaMate"
},
"definition": "select extract(epoch from max(end_date)) * 1000 from drives where $__timeFilter(start_date)",
"hide": 2,
"includeAll": false,
"multi": false,
"name": "to",
"options": [],
"query": "select extract(epoch from max(end_date)) * 1000 from drives where $__timeFilter(start_date)",
"refresh": 2,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"type": "query"
}
]
},
"time": {
"from": "now-1d",
"to": "now"
},
"timepicker": {
"hidden": false,
"time_options": [
"5m",
"15m",
"1h",
"6h",
"12h",
"24h",
"2d",
"7d",
"30d"
]
},
"timezone": "",
"title": "Road Trip",
"uid": "bdh3rtgqmblkwc",
"version": 34,
"weekStart": ""
}
@to-masz
Copy link
Author

to-masz commented Apr 2, 2024 •
edited

TODO:

  • Bring original support for multiple cars
  • Optimize queries and overrides
  • Green color for AC charging
  • Add parking/sleeping with new color
  • Check light theme appearance
  • Start/end position on the map

image
inspired by
image
https://www.facebook.com/photo/?fbid=10210952443684060&set=g.327571401932522

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment