Consumption per Month dashboard

[riesi77]

What would you like to be added:
I would love to see a new dashboard giving me the overview of my consumption (Wh/km) per month (maybe independent of the year)

Why is this needed:
It would give me a great overview of my consumption dependent of the season (winter vs. summer)

Additional context:

[pihomeserver]

Something like that ? (got my car this year in june)

[riesi77]

Exactly. My original thought was to make it independent of the year (so October 2020 would go into October as well as October 2021) - but I'm not sure if this is a good idea. Having the Distance in the dashboard is better.

[pihomeserver]

This version groups by month without taking the year into account
If you confirm that you can create dashboards once authenticated i can share with you the JSON file file of the dashboard

[riesi77]

I tried - but there is no new dashboard button (using the docker installation).

[pihomeserver]

Docker too.
On bottom left you have a user icon, you log in with credentials admin/admin and no need to change the password

[riesi77]

Nice. Yes. Now I can add new dashboards. If you are so kind and provide me your json, I can add it. However, for the product it would be great to have that contained in a future update, too.

[pihomeserver]

Here is my version. Could be improved i guess :
Maybe the uid (at the end shoud be updated by another id ?

{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": "-- Grafana --",
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "gnetId": null,
  "graphTooltip": 0,
  "id": 23,
  "iteration": 1602000960896,
  "links": [
    {
      "icon": "dashboard",
      "tags": [],
      "title": "TeslaMate",
      "tooltip": "",
      "type": "link",
      "url": "[[base_url:raw]]"
    },
    {
      "asDropdown": true,
      "icon": "external link",
      "tags": [
        "tesla"
      ],
      "title": "Dashboards",
      "type": "dashboards"
    }
  ],
  "panels": [
    {
      "collapsed": false,
      "datasource": null,
      "gridPos": {
        "h": 1,
        "w": 24,
        "x": 0,
        "y": 0
      },
      "id": 4,
      "panels": [],
      "repeat": "car_id",
      "scopedVars": {
        "car_id": {
          "selected": false,
          "text": "Pam ;)",
          "value": "1"
        }
      },
      "title": "$car_id",
      "type": "row"
    },
    {
      "columns": [],
      "datasource": "TeslaMate",
      "fontSize": "100%",
      "gridPos": {
        "h": 24,
        "w": 24,
        "x": 0,
        "y": 1
      },
      "id": 2,
      "links": [],
      "pageSize": 100,
      "pluginVersion": "6.7.4",
      "scopedVars": {
        "car_id": {
          "selected": false,
          "text": "Pam ;)",
          "value": "1"
        }
      },
      "scroll": true,
      "showHeader": true,
      "sort": {
        "col": 7,
        "desc": true
      },
      "styles": [
        {
          "alias": "Distance",
          "align": "auto",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "link": false,
          "mappingType": 1,
          "pattern": "distance",
          "thresholds": [
            ""
          ],
          "type": "number",
          "unit": "none"
        },
        {
          "alias": "Temperature",
          "align": "auto",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 1,
          "mappingType": 1,
          "pattern": "outside_temp_c",
          "thresholds": [],
          "type": "number",
          "unit": "degree"
        },
        {
          "alias": "Consumption",
          "align": "auto",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "consumption_kwh_100",
          "thresholds": [],
          "type": "number",
          "unit": "none"
        },
        {
          "alias": "",
          "align": "auto",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "month",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        }
      ],
      "targets": [
        {
          "alias": "",
          "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    TO_CHAR((duration_min * INTERVAL '1 minute'), 'HH24:MI') as duration_str,\n    drives.id as drive_id,\n    -- Columns\n    start_date,\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\n      -- ((start_position.battery_level - start_position.usable_battery_level) = (end_position.battery_level - end_position.usable_battery_level)) OR\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  FROM drives\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 cars car ON car.id = drives.car_id\n  WHERE $__timeFilter(start_date) AND drives.car_id = $car_id\n  --WHERE drives.car_id = $car_id\n  ORDER BY start_date DESC\n)\nSELECT\n--  start_date as time,\n  date_part('month', start_date) as month,\n  TO_CHAR(start_date, 'Month') AS month_name,\n  --TO_CHAR(TIMESTAMP, start_date, 'month)',\n  ROUND(SUM(distance)::numeric, 2) || ' $length_unit' as distance,\n  AVG(convert_celsius(outside_temp_avg, '$temp_unit')) AS outside_temp_$temp_unit,\n  ROUND(AVG(range_diff * car_efficiency / distance  * 1000 * CASE WHEN '$length_unit' = 'km' THEN 1\n                                                                                             WHEN '$length_unit' = 'mi' THEN 1.60934\n                                                                                        END)::numeric, 2) || ' Wh/$length_unit' AS consumption_kWh_100\nFROM data\nWHERE is_sufficiently_precise\nGROUP BY month, month_name\nORDER BY month\n",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "column"
              }
            ]
          ],
          "timeColumn": "time",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            }
          ]
        }
      ],
      "timeFrom": null,
      "timeShift": null,
      "title": "Drive",
      "transform": "table",
      "type": "table"
    }
  ],
  "schemaVersion": 22,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": [
      {
        "allValue": null,
        "current": {
          "selected": false,
          "text": "All",
          "value": "$__all"
        },
        "datasource": "TeslaMate",
        "definition": "SELECT name AS __text, id AS __value FROM cars;",
        "hide": 2,
        "includeAll": true,
        "index": -1,
        "label": "Car",
        "multi": false,
        "name": "car_id",
        "options": [],
        "query": "SELECT name AS __text, id AS __value FROM cars;",
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 5,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {
          "text": "C",
          "value": "C"
        },
        "datasource": "TeslaMate",
        "definition": "select unit_of_temperature from settings limit 1;",
        "hide": 2,
        "includeAll": false,
        "index": -1,
        "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": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {
          "text": "km",
          "value": "km"
        },
        "datasource": "TeslaMate",
        "definition": "select unit_of_length from settings limit 1;",
        "hide": 2,
        "includeAll": false,
        "index": -1,
        "label": "length unit",
        "multi": false,
        "name": "length_unit",
        "options": [],
        "query": "select unit_of_length from settings limit 1;",
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {
          "text": "rated",
          "value": "rated"
        },
        "datasource": "TeslaMate",
        "definition": "select preferred_range from settings limit 1;",
        "hide": 2,
        "includeAll": false,
        "index": -1,
        "label": "",
        "multi": false,
        "name": "preferred_range",
        "options": [],
        "query": "select preferred_range from settings limit 1;",
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {
          "text": "http://192.168.1.16:4000",
          "value": "http://192.168.1.16:4000"
        },
        "datasource": "TeslaMate",
        "definition": "select base_url from settings limit 1;",
        "hide": 2,
        "includeAll": false,
        "index": -1,
        "label": "",
        "multi": false,
        "name": "base_url",
        "options": [],
        "query": "select base_url from settings limit 1;",
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      }
    ]
  },
  "time": {
    "from": "now-3000d",
    "to": "now"
  },
  "timepicker": {
    "refresh_intervals": [
      "5s",
      "10s",
      "30s",
      "1m",
      "5m",
      "15m",
      "30m",
      "1h",
      "2h",
      "1d"
    ],
    "time_options": [
      "5m",
      "15m",
      "1h",
      "6h",
      "12h",
      "24h",
      "2d",
      "7d",
      "30d"
    ]
  },
  "timezone": "",
  "title": "Drives Copy",
  "uid": "VLAqKsZgz",
  "variables": {
    "list": []
  },
  "version": 10
}

[riesi77]

Very cool. Finally I managed to import it (also change the car's name). Thanks for your support. A very useful dashboard!

[shackrock]

How do I add this JSON? I keep getting an error "The dashboard has been changed by someone else" when I go to a new dashboard, then settings, then try to paste this JSON in.

Also curious if this will remain even after updates to the docker image?

[DrMichael]

Well, you rather should save it to a file and then import it.

It should remain when you only update the image. Could break though, when database got changes.

[pihomeserver]

Correct for the import

Also the dashboard will be lost for any new Grafana build as Adrian will reimport JSON of existing dashboards in the new Docker image. You have similar information in the dashboard called "statitics"