Grafana or InfluxDB - use date field instead of automated time field on x axis

  • What Grafana version and what operating system are you using?

Latest version in HA

  • What are you trying to achieve?
    Due to smart meter issues in Home Assistant, I have had to resort to a method that populates a sensor with the previous days data in the early hours of the morning. This means the timestamp for my data is the following day to the actual data. As a “workaround”, I save the correct date as an attribute (field) when the sensor data is retrieved.

This results in 2 issues:

  1. The utility sensor Reading_Date value has no defined type due to the constraints of template sensor definitions. The date string is a valid format for a days date but just not defined as such.
  2. So far, I can only see that the x axis can be an actual sensor update date so it shows the following day. Also, I have attempted to at least puick up historical data for the last month and updated the sensor with these value but, obviously, if I use the update timestamp on the x axis, instead of the Reading_Date value, it makes no sense as all values show within a few minutes of when they were populated.

Is there any means, via the Time Series Panel or any other alternative, to show the data values using the Reading_Date field as the X axis? One value per day would be great.

I am using InfluxDB and InfluxQL

HA Node Red process populates my sensor each day.

Runs at 00:20 every morning. Using glowmarkt api, retrieves data for the previous 24 hours into 4 helpers:

input_number.todays_elec_cost
input_number.todays_elec_consumption
input_number.todays_gas_cost (although some calculations are required to get the gas cost as the smart meter issues mean this is always set to zero so tariff and gas_consumption are used in the calculation)
input_number.todays_gas_consumption
input_datetime.utility_reading_date

The Home Assistant sensor I have created to use in Grafana is populated on change of these values:

    utilities_sensor:
      friendly_name: "Gas and Electric Cost"
      unit_of_measurement: "£"
      value_template: "{{ (( states('input_number.todays_gas_cost') | float / 100 ) + ( states('input_number.todays_elec_cost') | float / 100 )) | round(2) }}" 
      attribute_templates: 
        readings_date:  "{{ ( states('input_datetime.utility_reading_date')) }}"
        gas_consumption: "{{ ( states('input_number.todays_gas_consumption') | float ) | round(6) }}"
        electric_consumption: "{{ ( states('input_number.todays_elec_consumption') | float ) | round(6) }}"
        gas_cost_pounds: "{{ ( states('input_number.todays_gas_cost') | float / 100 ) | round(2) }}"
        elec_cost_pounds: "{{ ( states('input_number.todays_elec_cost') | float / 100 ) | round(2) }}"
        gas_tariff_pence: "{{ ( states('input_number.gas_tariff_in_pence') | float ) | round(6) }}"
        elec_tariff_pence: "{{ ( states('input_number.electricity_tariff_in_pence') | float ) | round(6) }}"
        gas_standing_charge_pence: "{{ ( states('input_number.gas_standing_charge_in_pence') | float ) | round(6) }}"
        elec_standing_charge_pence: "{{ ( states('input_number.electricity_standing_charge_in_pence') | float ) | round(6) }}"

I accept there will be a timestamp for these values to be appearing on a graph with the values at the time they were added but am not aware that this is something I can manipulate. My InfluxDB yaml is pretty simple:

host: localhost
database: home_assistant_history
username: !secret influxdb_user
password: !secret influxdb_password

Maybe there is something I can define in InfluxDB yaml to populate the timestamp for this specific Home Assistant sensor data with the input_datetime.utility_reading_date??

I am attaching an image of some of the InfluxDB data that I have created so far. This does also show the issue that my individual fields are all being defined as “£” values and also that it is creating a row for the update of each input helper so I get 5 rows instead of 1. The latter, I am fixing with a trigger so not concerned about that.

I have raised this in the Grafana community but guess the solution may lie here in HA communities.

Using datetime format field for time sequence on x axis

Did you ever solve this? I want to do exactly the same!