Using datetime format field for time sequence 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.

What is your datasource? If InfluxDB, are you using InfluxQL or Flux?

Please tell us more about your “method that populates a sensor with the
previous day’s data in the early hours of the morning.”

I’m not sure you mean “populates a sensor” - I assume you mean something more
like “inserts into a database”, so as Grant has already asked, which type of
database is this?

You say that “I save the correct date as an attribute (field) when the sensor
data is retrieved.” Surely there is already a datestamp field in your database
(otherwise Grafana would not know when any data point refers to) before
implementing this workaround, so why not also populate the datestamp field when
inserting yesterday’s data?

Antony.

InfluxDB and InfluxQL

HA Node Red process.

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.

What is the above exactly? Does it use one of these nodes to populate your data into Influx? If yes, your best bet is to correct the timestamp before sending it into InfluxDB.

Home Assistant automatically picks up changes in sensor values on its own database. The integration with influxdb recognises all of these value changes for any fields in the sensors and sends a copy of this to the influxdb home_assistant_history database.

The image is a select statement on the sensor i described above thatvshows several of the fields including the allocated time field.

I don’t physically set a value for the time. It is done by the integration and is a timestltamp of when the values eere changed.

I actually don’t know if this home assistant influxdb integration can be tailored to override the time value with one of my own but it is certainly not explicitly available to me via normal user interfaces. I have worked with other databases before and know in sql you can have scripts attached to tables and triggered by inserts/updates/deletes that can alter values in said tables but i dont know whether anything like that can be done in HA database, influxdb integration in HA or any other method. I was just hoping that i could utilise my extra workaround utility reading timestamp within Grafana.

It may be that Grafana will only work with thevautomatically allocated time value in which case, i may need to ask influxdb or HA community users.