Some charts misbehaving since v32 database schema change

[ntompson]

Checklist

• [x] I updated the card to the latest version available
• [x] I cleared the cache of my browser
• [x] I verified that I'm really running the lastest version in my browser console
• [x] I checked if there is another issue opened with the same problem

Describe the bug Since upgrading to HA 2023.2.x (currently on 2023.2.4), some of my ApexCharts are misbehaving. My guess is that the problems are related to the recently implemented v32 recorder database schema change. In this change, the last_updated column (as a datetime timestamp) has been deprecated in favour of last_updated_ts (as a unix timestamp). The last_updated column remains present but is no longer getting populated; the last_updated_ts column is retrospectively populated at time of schema migration.

The blog article suggests that users of the recorder history API are not impacted by the change, but I wonder...

I've seen two symptoms.

• Stacked charts are no longer stacking properly (even though each series plots correctly if displayed individually by clicking off the others series names).
• Series with first value appearing mid-way through the x-axis time series are no longer rendering to the left hand side of the chart, which they used to do.

Version of the card Version: v2.0.22

To Reproduce

First case

Relevant template excerpt for stacked charts

storage_chart:
  config_templates: default
  graph_span: 7d
  stacked: true

  all_series_config:
    stroke_width: 0
    opacity: 1
    group_by:
      func: avg
      duration: 10min

  color_list:
    - '#EB8967'
    - '#FFC88D'
    - '#64955B'
    - '#B6E5AE'

  yaxis:
    - apex_config:
        title:
          text: Storage (GB)
      min: 0

Second case

Relevant excerpt for series chart not rendering to left handside.

type: custom:apexcharts-card
config_templates: default
graph_span: 6h

series:
  - entity: sensor.myenergi_home_power_grid
    transform: "return x / 1000;"
    unit: kW
    name: Import / export
    color: '#e8e8e8'
    opacity: 1
    stroke_width: 0

  - entity: sensor.ev_public_charging_power
    name: Public
    color: '#D61635'

  - entity: sensor.ev_charging_total_power
    transform: "return x / 1000;"
    unit: kW
    name: Solar
    color: '#48983B'

  - entity: sensor.ev_charging_grid_power
    transform: "return x / 1000;"
    unit: kW
    name: Grid
    color: '#275CD0'

yaxis:
  - apex_config:
      title:
        text: kW
    min: -2
    max: ~3

Screenshots

First case

Goofy stacked chart. Note "/home/used" series:

Looking only at "home/used" series in isolation as an example (correct rendering - all series are similarly flat over the period):

Second case

Note blue and green series only start rendering on right side of chart. No recorder entries to the left of the chart within the x-axis timeframe, but in previous versions, this section of the series correctly rendered as zero with fully connected lines.

Expected behavior In both cases, expected behaviour occurred before HA 2023.2.x upgrade.

• For first case, stacked charts rendered correctly without strange vertical jumps as currently seen
• For second case, even when recorder entries were not available in part of the x-axis time window, all series fully rended from left to right across the x-axis

Desktop (please complete the following information): All platforms (desktop, smartphone) show the same behaviour. Have tried:

• Desktop Chrome (macOS) 109.0.5414.119
• Desktop Safari (macOS) 16.3 (18614.4.6.1.5)
• HA iOS Companion app (2023.2 (2023.444))

[roobre]

Im having an (I believe) similar issue since the upgrade to 2023.2, where I get seemlingly random lines connecting points on the past with the latest value in the chart.

If I don't use a group_by aggregation (which I tend not to when I want charts to display the latest value without lag), a very large number of these spurious lines appear (not pictured) and make the UI extremely laggy, taking several seconds to handle mouse input.

Adding a group_by works around this behavior, but it still a bit annoying having to do so.

[andrzej-r]

Not sure if this is the same issue but I get the following result (non-zero values after "now"): Yesterday everything looked normal, today it looks like the current measurement (not the average) is extended till the end of the day. In the morning I have also observed problems with stacking but now it is fine. Tested with area and line plot types, with and without stacking. Experimented with fill:zero and start_with_last:true but they do not have any effect.

Configuration:

type: custom:apexcharts-card
graph_span: 1d
span:
  end: day
  offset: '-0day'
now:
  show: true
  color: red
stacked: true
all_series_config:
  stroke_width: 1
  opacity: 0.5
  type: area
  group_by:
    func: avg
    duration: 10min
    fill: zero
    start_with_last: true
series:
  - entity: sensor.solar_to_house
    name: to house
  - entity: sensor.solar_to_battery
    name: to battery
  - entity: sensor.solar_to_grid
    name: to grid

[github-actions[bot]]

This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 10 days.

[ntompson]

Sorry to be annoying... is there anything else I can do to help diagnose this bug?

[cosote]

I hope my findings are related and might help some facing similar issues when using stacked bars that don't add up. I fixed it by adding 0 values for every missing time value across all series. I'm using victoria metrics and changed my data_generator from:

    let request = async () => {
      let a = [], r = await fetch(`http://${new URL(hass.auth.data.hassUrl).hostname}:8428/prometheus/api/v1/query_range?` +
      new URLSearchParams({query: `${entity.entity_id}_value`,start: `${start/1000}`,step: "30m"})), j = await r.json();
      if (j.data.result[0]) for (let r of j.data.result[0].values) a.push([r[0]*1000, r[1]]);
      return a;
    };
    return request();

to:

    let _start = start / 1000, _now = new Date() / 1000, request = async () => {
      let a = [], r = await fetch(`http://${new URL(hass.auth.data.hassUrl).hostname}:8428/prometheus/api/v1/query_range?` +
      new URLSearchParams({query: `${entity.entity_id}_value`,start: _start, step: "30m"})), j = await r.json();
      if (j.data.result[0]) {
        let v = j.data.result[0].values;
        let h = 0;
        for (let i = 0; i < 96; i++) {
          let t = _start + i * 30 * 60;
          if (t > _now) break;
          if (v.length == h) {
            a.push([t * 1000, 0]);
            continue;
          }
          let r = v[h];
          if (r[0] == t) { 
            a.push([r[0] * 1000, r[1]]);
            h++;
          } else {
            a.push([t * 1000, 0]);
          }
        }
      }
      return a;
    };
    return request();

The graph line is approx. the total of the stacked bars... the original graph was corect on the total line, but the sum of the stacked bars was completely off and is missing many values:

Now, with the adjusted script, the numbers match my expectations:

[github-actions[bot]]

This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 10 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 10 days with no activity.