energy-gas-graph shows gas usage in the next hour

Checklist

[X] I have updated to the latest available Home Assistant version.
[X] I have cleared the cache of my browser.
[X] I have tried a different browser to see if it is related to my browser.

Describe the issue you are experiencing

Gas used at 05:30 is shown in the bar of 06:00 - 07:00. This should however be included in the 5:00 - 6:00 bar instead. Below image shows a shower taken at 05:30: The water consumption is correctly shown in the 05:00 - 06:00 bar, but the gas consumption is incorrectly listed in the 06:00 - 07:00 bar.

Describe the behavior you expected

I expect the Gas consumption to be listed in the same time window of 05:00 - 06:00 as the water consumption.

Steps to reproduce the issue

Have a DSMR4 P1 meter (Dutch slimme meter) for the input: The DSMR4 and older meters only report gas consumption once each hour. DMSR5 meters report gas consumption each 5 minutes. For these the same error also occurs but it is less obvious, as it is only a 5 minute period of consumption that will be reported incorrectly.
Have a gas powered DHW (domestic hot water) installation.
Turn on the hot water at 05:30 for some time.
Wait until the clock has struck the next hour (recorder has collected the previous hour's data). The gas consumption will be recorded into the statistics table with a start_ts representing YYYY-mm-dd 06:00:00.000.
Now open the Energy dashboard. It will show the gas consumption in the 06:00 - 07:00 bar (You may have to wait another hour before it actually shows the data).

What version of Home Assistant Core has the issue?

Home Assistant 2023.7.1 Supervisor 2023.07.1 Operating System 10.3 Frontend 20230705.1 - latest

What was the last working version of Home Assistant Core?

N/A

In which browser are you experiencing the issue with?

Any

Which operating system are you using to run this browser?

windows, android

State of relevant entities

sensor.gas_meter_gas_consumption
Gas Meter Gas consumption
6198.711    state_class: total_increasing
unit_of_measurement: m³
device_class: gas
friendly_name: Gas Meter Gas consumption

Problem-relevant frontend configuration

Energy dashboard configuration can not be defined in yaml. Mine has the single sensor "Gas Meter Gas consumption" for Gas Consumption. This has id 33 in statistics_meta.

Javascript errors shown in your browser console/inspector

no errors are shown, no logs are created.

Additional information

An example of the incorrect output of the energy-gas-graph card: Gas used at 5:30 is listed in the 06:00 - 07:00 bar, whereas the water used by the same shower is listed in the 05:00 - 06:00 bar.

Image of Energy dashboard configuration:

I ran following sqlite query to list the contents of my statistics table, as that is what is used to read the graph data from. Times are in UTC, I am in UTC+2. metadata_id = 33 is the id from statistics_meta for my gas meter consumption sensor's statistics:

select strftime('%d-%m-%Y %H:%M:%f', ss.start_ts, 'unixepoch') as start_dt, strftime('%d-%m-%Y %H:%M:%f', se.start_ts, 'unixepoch') as end_dt, ss.start_ts, se.start_ts as end_ts, ss.[sum] as start_sum, se.[sum] as end_sum, se.[sum] - ss.[sum] as [difference] from statistics_short_term ss inner join statistics_short_term se on se.metadata_id = ss.metadata_id and se.start_ts > ss.start_ts and not exists (select * from statistics_short_term se2 where se2.metadata_id = ss.metadata_id and se2.start_ts > ss.start_ts and se2.start_ts < se.start_ts) where ss.metadata_id = 33 and ss.start_ts >= (julianday('2023-07-13T03:00') - 2440587.5)*86400.0 and ss.start_ts < (julianday('2023-07-13T04:00') - 2440587.5)*86400.0 order by ss.start_ts desc limit 300;

The query results in the following output:

start_dt|end_dt|start_ts|end_ts|start_sum|end_sum|difference
13-07-2023 03:55:00.000|13-07-2023 04:00:00.000|1689220500.0|1689220800.0|127.096|127.225|0.129000000000815
13-07-2023 03:50:00.000|13-07-2023 03:55:00.000|1689220200.0|1689220500.0|127.096|127.096|0.0
13-07-2023 03:45:00.000|13-07-2023 03:50:00.000|1689219900.0|1689220200.0|127.096|127.096|0.0
13-07-2023 03:40:00.000|13-07-2023 03:45:00.000|1689219600.0|1689219900.0|127.096|127.096|0.0
13-07-2023 03:35:00.000|13-07-2023 03:40:00.000|1689219300.0|1689219600.0|127.096|127.096|0.0
13-07-2023 03:30:00.000|13-07-2023 03:35:00.000|1689219000.0|1689219300.0|127.096|127.096|0.0
13-07-2023 03:25:00.000|13-07-2023 03:30:00.000|1689218700.0|1689219000.0|127.096|127.096|0.0
13-07-2023 03:20:00.000|13-07-2023 03:25:00.000|1689218400.0|1689218700.0|127.096|127.096|0.0
13-07-2023 03:15:00.000|13-07-2023 03:20:00.000|1689218100.0|1689218400.0|127.096|127.096|0.0
13-07-2023 03:10:00.000|13-07-2023 03:15:00.000|1689217800.0|1689218100.0|127.096|127.096|0.0
13-07-2023 03:05:00.000|13-07-2023 03:10:00.000|1689217500.0|1689217800.0|127.096|127.096|0.0
13-07-2023 03:00:00.000|13-07-2023 03:05:00.000|1689217200.0|1689217500.0|127.096|127.096|0.0

In my opinion the statistics data has been stored correctly by the recorder: the single reported consumption value is correctly registered with a timestamp in the 05:00 - 06:00 (UTC + 2) time range between 03:55:00.000 and 04:00:00.000 (UTC). The first higher value is in the row with the 04:00:00.00 time stamp. The energy gas card however has selected for the 05:00 - 06:00 bar the data between 05:00 (inclusive) and 06:00 (exclusive), thus not finding the 5:30 gas consumption. For the 06:00 - 07:00 bar it selected the range 06:00 (inclusive) - 07:00 (exclusive) and thereby listed the 5:30 gas consumption in the 6-7 bar. Because gas consumption is non-instantaneous, each measurement's value will by definition always become available at the end of the measurement time frame, meaning the end of the time frame should be included in the selection , not the start. I suggest therefor that the energy-gas-graph card should select data between 05:00 (exclusive) and 06:00 (inclusive) for the 05:00 - 06:00 bar and between 06:00 (exclusive) and 07:00 (inclusive) for the 06:00 - 07:00 bar.

home-assistant / frontend