-
-
Save to-masz/d946e716c83f028b1a90319d7a404678 to your computer and use it in GitHub Desktop.
[WIP] Custom trip dashboard for TeslaMate/Grafana
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"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": "" | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
TODO:
inspired by
https://www.facebook.com/photo/?fbid=10210952443684060&set=g.327571401932522