Support Energy date picker?

[totalitarian]

It would be great if we could put an apex chart on a page containing type: energy-date-selection as mentioned here https://www.home-assistant.io/dashboards/energy/ and the apex chart adjust to match the dates/period in the picker. I'm sure it's possible using a combination of config template card and this websocket

const fetchStatistics = (
  hass: HomeAssistant,
  startTime: Date,
  endTime?: Date,
  statistic_ids?: string[],
  period: "5minute" | "hour" | "day" | "week" | "month" = "hour",
  // units?: StatisticsUnitConfiguration
) =>
  hass.callWS<Statistics>({
    type: "recorder/statistics_during_period",
    start_time: startTime.toISOString(),
    end_time: endTime?.toISOString(),
    statistic_ids,
    period,
    // units,
  });

but im yet to figure out how.

Any idea?

[rrozema]

I second this request. It would enable a lot more entities to be graphed. This would be most useful to show data held in entities with state_class: total or state_class: total_increasing. For these entities the data is stored in the statistics table and it looks like a time-series table: column start_ts in the statistics table represents the time column (in UTC). The value to show is however harder to retrieve. Each time-frame's value must be calculated by taking the difference of the [sum] columns on 2 rows: the [sum] value of the row at the end of the time frame minus the [sum] value of the last row before the start of the time-frame.

The recorder stores the statistics in 2 different time series:

• a short term statistic, stored in the statistics_short_term table. The data in this table gets purged after a couple of days (can be configured by the user). The data points are 5 minutes apart in the short term statistics.
• a long term statistic, stored in the statistics table. The data in the statistics table is kept indefinitely. The data points in the long term statistics are 1 hour apart.
  Due to the way the total and total_increasing statistics are stored 2 rows need to be retrieved for a single data point. An extra complication occurs when a gap exists in the data: a row with the last state_ts value before the selected time-frame -that is needed to determine the actual value inside this time frame- may be missing. For this reason one must always perform a query for this last row before the selected time frame. We can never assume the previous row of time T to have a start_ts value of T minus 1 hour... For example to get the number of steps per hour reported yesterday by my android phone's companion app:

select strftime('%Y-%m-%d %H:%M:%f', se.start_ts, 'unixepoch') as [time], se.[sum] - ss.[sum] as [value] from statistics se inner join statistics_meta m on m.id = se.metadata_id left outer join statistics ss on ss.metadata_id = se.metadata_id and ss.start_ts < se.start_ts and not exists (select * from statistics ss2 where ss2.metadata_id = se.metadata_id and ss2.start_ts < se.start_ts and ss2.start_ts > ss.start_ts) where se.start_ts >= strftime('%s', datetime('now', 'start of day', '-1 days')) and se.start_ts < strftime('%s', datetime('now', 'start of day')) and m.statistic_id = 'sensor.mi11_richard_steps_sensor' order by se.start_ts limit 200;

Which results in this table:

time|value
2023-08-13 00:00:00.000|0.0
2023-08-13 01:00:00.000|0.0
2023-08-13 02:00:00.000|0.0
2023-08-13 03:00:00.000|0.0
2023-08-13 04:00:00.000|0.0
2023-08-13 05:00:00.000|0.0
2023-08-13 06:00:00.000|12.0
2023-08-13 07:00:00.000|0.0
2023-08-13 08:00:00.000|83.0
2023-08-13 09:00:00.000|0.0
2023-08-13 10:00:00.000|0.0
2023-08-13 11:00:00.000|1298.0
2023-08-13 12:00:00.000|686.0
2023-08-13 13:00:00.000|252.0
2023-08-13 14:00:00.000|0.0
2023-08-13 15:00:00.000|56.0
2023-08-13 16:00:00.000|117.0
2023-08-13 17:00:00.000|0.0
2023-08-13 18:00:00.000|0.0
2023-08-13 19:00:00.000|0.0
2023-08-13 20:00:00.000|0.0
2023-08-13 21:00:00.000|0.0
2023-08-13 22:00:00.000|0.0
2023-08-13 23:00:00.000|0.0

To get the values per day, week, month, etc. one would simply sum up all the hourly values for that day, week, month, etc..

I very much would like to be able to create a graph from these total and total_increasing statistics, especially using the energy dashboard's date selection card as the selection criterium for the time-frame as was suggested by the OP.

To give you an impression of which data could be easily put into a graph once this gets implemented, run the following query against your own homeassistant db:

select statistic_id, unit_of_measurement, id from statistics_meta where has_sum = 1 order by statistic_id;

[mdallaire]

The Sankey Chart card supports having a date selector. It might be a good reference on how to implement it here.

[ManuelKugelmann]

I'm currently thinking about turning this around: There is no real reason all the energy dashboard functionality is as encapsulated as it is - guess that is an unfortunate side effect of it being its own little project. What we really want is a generic date and time-span navigator card that makes the data easily available to templates e.g. as part of the global hass object. Then all the various cards just have to habe their time range input offsettable and templateable (looking at you, mini-graph-card) ...

[FlorianWilk]

I like the idea, altough it sounds like a lot of work. And it somehow needs to work with the current energy-date-picker, so we don't need two date-pickers for custom energy-dashboards.

[ManuelKugelmann]

I'm thinking of leveraging the existing custom energy date picker and exposing it's data via java script globals for other cards to grab. Should not be too much work ... but as always, availabe time depends on other workloads :)

[FlorianWilk]

I think that's a very good idea. I would be very happy if you did that ;) It would really enhance the functionality.

[rrozema]

The implementation of sankey-chart for retrieving the change values has a flaw when a gap exists in the statistics data aggregated from an entity with state_class: total or state_class: total_increasing (= a statistic where has_sum = 1). Such a gap will exist f.e. when a device has been unavailable for some time.

When the start of the selected period falls inside such a gap, the total 'change' value returned for this period will include the actual value from one of the rows' [sum] column instead of summing the difference between that row and the row before it. In other words, the change value will be shown (much) higher than it actually is, even though no such high values can be found when querying the database. The high value appears only when no data is available at the timestamp of the start of the period. If for example the start of the date range is changed to an earlier point in time (where data is available), the change values shown will no longer include such a high value.

If you do use the sankey-chart implementation as an example, please do keep this in mind.