Offset Date for Sensor Values?

Hi all! I have a module loaded into Node-RED that is able to fetch power usage data from my power company, but can only fetch the prior day’s data, so this morning it fetched yesterday. It publishes over mqtt currently, but I’m not averse to modules to integrate directly if it would help, but, is there a way to force this data to apply to the previous date in Home Assistant? I eventually am going to install CT clamps to monitor live, and I’d like to be able to compare measured usage against what my power company reports. Any suggestions are welcome if anyone’s got experience with this, as I couldn’t find a means of doing this myself.

1 Like

The best solution will probably vary depending on how you ultimately want to use the data – which may be partially dictated by the data provided by your CT clamps.

But if you’re bringing the data into Home Assistant via template sensor(s) you could use attribute_templates.

Something like…

attribute_templates: >
      {{ now().day -1 }}

…which would return the day of the week (eg: “6”) minus 1 (“5”).

Then if you bring the CT clamp data in similarly you could just omit the -1 in its template sensor configuration.

I went ahead and tried pulling the data into a template since it’s coming in by way of MQTT, but it seems to want a key for that attribute, as a dictionary of attributes. Checking states.sensor.daily_power_usage doesn’t show a name for the timestamp, there’s just the @ in front.

Re-reading, I’m unfortunately not pulling the date itself in, just the data, oops. I suspect I may not be able to do what I want to with this.

Oh yeah I overlooked the provided syntax:

I’ve haven’t used attribute_templates before (I think it’s a pretty new feature) but it looks like the syntax should be more like:

  day: {{ now().day -1 }}

But if you’re creating a new sensor anyway then the day could just be the state.

value_template: >
      {{ now().day -1 }}

Sorry, I got interrupted while posting. What data are you pulling in, and in what form; binary_sensor?

So far, just pulling publishing the value of, in this case, yesterday’s power usage in kWh over MQTT, and then wanting to chart it with the values applying to the appropriate day.

Sorry, I’m not too familiar with MQTT. I think you’ll need a sensor in HA no matter what. If you don’t currently have a sensor for this you could create one that either gets updated directly from MQTT, or from Node-RED. In either case the sensor will make two timestamps available as attributes: last_updated and last_changed (more info).

So you seem to have several options at your disposal:

  • Bring both datasets into Node-RED, perform date calculations/comparisons there, and pass the results to one or more sensor/s in HA.

  • Bring both datasets into Node-RED, pass the results to one or more sensor/s in HA, and perform date calculations/comparisons in HA:

    • Date calculations/comparisons could be included as templates in the initial sensor/s receiving the data
    • Date calculations/comparisons could be performed by additional sensor/s using the data from the initial sensor/s
  • Bring both datasets directly to one or more sensor/s in HA, and perform date calculations/comparisons in HA:

    • Date calculations/comparisons could be included as templates in the initial sensor/s receiving the data
    • Date calculations/comparisons could be performed by additional sensor/s using the data from the initial sensor/s

Lol, that’s a lot to consider, but you may find that some of these are less viable options as you get the additional hardware and really start zeroing in on your ultimate goal.

Did you ever figure out what to do? I am in the same boat. Really tough to properly graph data when actual data date is 24 hours behind what HA has for it.

While it’s not completely clear at first glance, notice that last_changed and last_updated are listed separately from the attributes section. In template format, these list as state.sensor.my_sensor.last_changed. However, I only have the ability to write to an attribute of the same name, which does not change the actual value, so instead it’s state.sensor.my_sensor.attributes.last_changed. If you try to access that directly in a template, or use state_attr('sensor.my_sensor','last_changed'), both will display blank since that isn’t where the datestamp is stored.

My only other solution might be to simply list the values using a template, if templates have access to value history, since I could then have date as one sensor and usage as another and simply list them.

I’m not clear if by ‘name’ you’re referring to the value itself being the same, or if you mean that even writing a new (different) value does not update last_changed.

Either way though you could just add a timestamp in Node-RED that’s applied each instance the data is fetched:

(in this example I am, ironically, using the template node from node-red-contrib-home-assistant-websocket)


[{"id":"ff84eecf.1ba04","type":"change","z":"93de0bd6.350048","name":"msg.payload.{{ now().day -1 }}","rules":[{"t":"set","p":"template","pt":"msg","to":"{{ now().day -1 }}","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1010,"y":2560,"wires":[["9aa72680.b3dc88"]]},{"id":"9aa72680.b3dc88","type":"api-render-template","z":"93de0bd6.350048","name":"","server":"e9beaddb.fc24e","template":"","resultsLocation":"","resultsLocationType":"msg","templateLocation":"template","templateLocationType":"msg","x":1260,"y":2560,"wires":[["708ccc2b.ede0c4"]]},{"id":"feb9a831.1f6878","type":"change","z":"93de0bd6.350048","name":"msg.payload.{\"power_usage\": \"data\"}","rules":[{"t":"set","p":"payload","pt":"msg","to":"{ \"power_usage\": \"data\" }","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":1030,"y":2500,"wires":[["ff84eecf.1ba04"]]},{"id":"708ccc2b.ede0c4","type":"debug","z":"93de0bd6.350048","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1270,"y":2620,"wires":[]},{"id":"51e547be.886038","type":"inject","z":"93de0bd6.350048","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":930,"y":2440,"wires":[["feb9a831.1f6878"]]},{"id":"e9beaddb.fc24e","type":"server","z":"","name":"Home Assistant","legacy":false,"addon":false,"rejectUnauthorizedCerts":false,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true}]

To clarify, the issue is not that last_changed or last_updated isn’t changing. Per your post I replied to, it seemed as if you were indicating those attributes so that I could manually set them to the date I desired.

That is not possible through the attributes setting. Let me clarify the context.

Today, Wednesday July 1st, at 9 AM, Node-RED fires off a flow that fetches data from my power company. The data fetched is for Tuesday, June 30th, because that data is not available in real time, and only the total usage for the day is available, the following day, by 9 AM.

As a result, if I graph the sensor that I supply that total usage to (the means in which this is supplied I don’t think is relevant at this time), the points on the graph show the incorrect date for each data point. Each usage day is labeled with the following day’s date. IE, power usage for the 1st is labeled as the 2nd, for the 2nd as the 3rd, and so on. See below:

The highlighted data point of 34 kWh is labeled as for Tuesday June 30th but is actually for Monday June 29th, and I want a way to be able to effectively label incoming data for such purposes if that’s possible. If I need to use another integration route, such as a DB where I can set dates, I am open to that option.

OK, if last_changed / last_updated are changing then they can be used as your input(s) for a new template_sensor. BTW, today being the 1st day of the month exposes a flaw in the now().day -1 method I had previously suggested as it’s returning 0 because the math is being blindly performed on the result instead of within the context of dates. That being said, the same concept still applies (getting the date the data is fetched and subtracting one day for your graph to display) but the method of doing so needs improvement. So right now you’re (presumably) able to retrieve the date and time the data was fetched using, {{ states.DOMAIN.ENTITY.last_updated }}…?
You can test this in “Developer Tools” > “TEMPLATE”, replacing DOMAIN.ENTITY with your sensor, eg: {{ states.sensor.alpow_usage.last_updated }}. This should return something like, 2020-07-01 20:38:18.561887+00:00.

Now if you add | as_timestamp like this,
{{ states.sensor.alpow_usage.last_updated | as_timestamp }}
you should get something like, 1593635898.561887.

Now we can subtract 86400 seconds (24 hours) like this,
{{ (states.sensor.alpow_usage.last_updated | as_timestamp - 86400) }}
to get something like, 1593549498.561887 (last_updated minus 24 hours).

To get it into the same format you’re now displaying you can add, | timestamp_custom('%d %a, %-I:%M %p') like this (cheat sheet),
{{ (states.sensor.alpow_usage.last_updated | as_timestamp - 86400) | timestamp_custom('%d %a, %-I:%M %p') }}
which should return something like, 30 Tue, 3:38 PM.

Your image shows two times (presumably ‘start’ and ‘end’) so if you want both to show you may need to figure out how those are input (separate sensors?).

Edit: Corrected dissimilar timestamps.

You template would look something like…


  - platform: template
        value_template: >
            {{ (states.sensor.alpow_usage.last_updated | as_timestamp - 86400) | timestamp_custom('%d %a, %-I:%M %p') }}
1 Like

Unless I am missing something, i dont see how creating a template sensor that adjusts the last_update value is going to help at all with charting data with the correct date. If a sensor saves the value today, its going to save it with todays timestamp and from what i have read, nothing is going change that. Charting solutions could care less what the last_updated value are. They get them from the timestamps stored in the db for the entries. I would love to be wrong, but I dont think thats the case here.

Yep, that’s the issue I was running into and wanted a solution to, but it appears there is not one. The closest thing would be to log power in one sensor and date in another, but I’d need something else that can actually chart that because there’s no way to chart date and a scalar-value in the same view.

Yes, that’s what my suggestion would accomplish. Sorry if I misunderstood. Based on this comment,

, I got the impression that you were only interested in how the graph was displayed in the Frontend. Your existing sensor and this new sensor could both be shown on a single card to achieve a look similar to the image you posted, but this would not change the timestamps in HA’s history.

You may be able to inject a timestamp from Node-RED as I had suggested earlier, but how feasible this is would depend on the means in which the sensor is supplied from NR to HA. Also, the now() method I previously posted would fail on the first day of each month, so it would need to be more like this:


[{"id":"ff84eecf.1ba04","type":"change","z":"93de0bd6.350048","name":"msg.template.{{ (now() | as_timestamp - 86400) | timestamp_custom('%d %a, %-I:%M %p') }}","rules":[{"t":"set","p":"template","pt":"msg","to":"{{ (now() | as_timestamp - 86400) | timestamp_custom('%d %a, %-I:%M %p') }}","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":920,"y":2600,"wires":[["9aa72680.b3dc88"]]},{"id":"9aa72680.b3dc88","type":"api-render-template","z":"93de0bd6.350048","name":"","server":"e9beaddb.fc24e","template":"","resultsLocation":"","resultsLocationType":"msg","templateLocation":"template","templateLocationType":"msg","x":1360,"y":2600,"wires":[["708ccc2b.ede0c4"]]},{"id":"feb9a831.1f6878","type":"change","z":"93de0bd6.350048","name":"msg.payload.{\"power_usage\": \"data\"}","rules":[{"t":"set","p":"payload","pt":"msg","to":"{ \"power_usage\": \"data\" }","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":1130,"y":2540,"wires":[["ff84eecf.1ba04"]]},{"id":"708ccc2b.ede0c4","type":"debug","z":"93de0bd6.350048","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1370,"y":2660,"wires":[]},{"id":"51e547be.886038","type":"inject","z":"93de0bd6.350048","name":"","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":1030,"y":2480,"wires":[["feb9a831.1f6878"]]},{"id":"e9beaddb.fc24e","type":"server","z":"","name":"Home Assistant","legacy":false,"addon":false,"rejectUnauthorizedCerts":false,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true}]

If you could bring this in to HA as a sensor with the “power_usage” key (your fetched power usage data) as the state and the “day” key as an attribute you’d at least have that as secondary information to reference in HA’s history. You would probably still need to customize your card in the Frontend to display this attribute instead of last_changed / last_updated though.

I’m on the same boat too. Still finding the way to correct import sensor data from 3rd data source to HA with correct timestamp.
I have bunch of offline sensor which collect data all day but only upload it to server at mid night. I want to storage everything at one place for easy export later. However HA doesn’t support or have any kind of service to inject sensor data with timestamp like that.

1 Like

Now I have the same issue, I would like to get energy meter reading from my utility, but I get them a day late - so the data shows up shifted by one day.

Same issue here. I don’t think there is a proper solution because state changes are recorded in the database and nothing can alter historical data.

So let’s talk about ugly solutions. The only possible way I can see is to modify the database directly with, say, an AppDaemon script. I have no idea how HA will respond to that, i.e. whether the values are cached in the memory or fetched every time a history graph or the energy dashboard is accessed. I will need to do some tests.

Any updates on this topic? I just realized the same issue setting up the energy page for the first time using data from my energy provider.