Switch to Summer time bug?

1

I have a graph with my energy usage over a month like this:

image
image1648×916 102 KB

I am storing values every 10 minutes in a sqlite database and then sums this in the chart.
As it can be seen the graph has a line from the “last day” of the previous month to the first of the month being shown. I think this started after we switched to summer time.
The URL looks like this: http://openhabsrv/rest/persistence/items/smartmeter_10_min_energy_usage?serviceId=jdbc&starttime=2024-02-29T23%3A00%3A00.000Z&endtime=2024-03-31T21%3A59%3A59.999Z and the first 6 datapoints equal the amount in the day from the last previous month.
The chart is made like this:

config:
  chartType: month
  label: Energy usage month
slots:
  grid:
    - component: oh-chart-grid
      config: {}
  series:
    - component: oh-aggregate-series
      config:
        aggregationFunction: sum
        dimension1: date
        gridIndex: 0
        item: smartmeter_10_min_energy_usage
        name: El forbrug
        service: jdbc
        type: line
        xAxisIndex: 0
        yAxisIndex: 0
    - component: oh-aggregate-series
      config:
        aggregationFunction: sum
        dimension1: date
        gridIndex: 0
        item: smartmeter_10_min_cost
        name: Pris
        service: jdbc
        type: bar
        xAxisIndex: 0
        yAxisIndex: 1
  tooltip:
    - component: oh-chart-tooltip
      config:
        confine: true
        orient: horizontal
        position: top
        show: true
  xAxis:
    - component: oh-category-axis
      config:
        categoryType: month
        gridIndex: 0
        monthFormat: default
        name: Day
        nameGap: 20
        nameLocation: center
        weekdayFormat: default
  yAxis:
    - component: oh-value-axis
      config:
        gridIndex: 0
        name: kWh
    - component: oh-value-axis
      config:
        gridIndex: 0
        name: Pris
        nameGap: 30

I am guessing this is a bug ?

2

Is nobody else seeing this ?

3

Seems odd.
Can you figure out where this rogue 0.151 state is within the HTTP response?

4

The first 6 datapoints 0,017 + 0,017 + 0,02 + 0,03 + 0,03 + 0,037 = 0,151.
So I guess that where the values come from.

5

looks to me like you are plotting 2 values where the day is 29 and that is why it is showing at 2 different points on the line chart.
edit removed comments around leap year after looking closer at time stamps in image
perhaps 2 plot points on day 29 due to limiting to Month of March?

6

Converting the timestamps they are indeed from February 29, at 22:09 UTC for the first one, so in the hour before the requested period should start. So it seems to be connected to DST in some way, but perhaps in the sqlite backend. Then the chart somehow decides to put them in the wrong month, possibly because it’s only looking at the day value and time.

7

yep, That query result as shown
Convert epoch to human-readable date and vice versa
1709244597069
Timestamp to Human date [batch convert]
Supports Unix timestamps in seconds, milliseconds, microseconds and nanoseconds.
Assuming that this timestamp is in milliseconds:
GMT: Thursday, February 29, 2024 10:09:57.069 PM

Relative: A month ago
and what the timestamp should have been returned for
Yr Mon Day Hr Min Sec
2024 -2 -29 23 : 30 : 00 GMT

Human date to Timestamp
Epoch timestamp: 1709249400
Timestamp in milliseconds: 1709249400000
Date and time (GMT): Thursday, February 29, 2024 11:30:00 PM
Still Not seeing how that is a code glitch in OpenHab …
Beyond the fact the graph is plotting for 2 different values for day 29 but since he is searching more data points then month of March but limiting the graph to only the month of March it is treating both values that are for measurements on a date of 29th as in same month And that may be by design as month of March as limiting parameter may expect all results to only be from the month of March.

8

I guess that depends on how and where the REST request gets converted to a SQL query for the sqlite DB. As for the plotting it seems to aggregate the values separately for the different days, but renders it incorrectly. This could be a bug in the plotting library, or a result of how OH is interacting with it, hard to tell without looking at the code.

Edit: and as you say, the plot is limited to a month, but the query is including the last hour of the previous month, (which could also be a DST bug). This could either be in MainUI code, plotting library or datetime library (or the interaction between any of them)

Edit2: the last part is likely due to timezone difference after some more thinking

9

yea I guess it would go back to which of these querys is being used to make the call to DB
SQLite SELECT datetime(epoch_to_convert, 'unixepoch'); or local timezone: SELECT datetime(epoch_to_convert, 'unixepoch', 'localtime');