[jdbc.mysql] Not all values showed in graph

[ghost]

@DanielMalmgren commented on Dec 9, 2020, 12:36 PM UTC:

The problem

All values aren't showed in the graph. It seem always the last 1-2 hours are missing.

The last value on the graph below is 58 lux, at 11:10:

At the same time I had the following values in my persistence database (pasting from mysql):
| 2020-12-09 11:00:32 | 40.00 |
| 2020-12-09 11:10:32 | 58.00 |
| 2020-12-09 11:20:32 | 23.00 |
| 2020-12-09 11:30:32 | 82.00 |
| 2020-12-09 11:40:32 | 106.00 |
| 2020-12-09 11:50:32 | 43.00 |
| 2020-12-09 12:00:32 | 30.00 |
| 2020-12-09 12:10:32 | 66.00 |

Expected behavior

The graph should show all values.

Steps to reproduce

1. Go to an Item in the UI
2. Click on Analyze

Your environment

runtimeInfo:
  version: 3.0.0.M5
  buildString: Milestone Build
locale: sv_SE
systemInfo:
  configFolder: /etc/openhab
  userdataFolder: /var/lib/openhab
  logFolder: /var/log/openhab
  javaVersion: 11.0.9.1
  javaVendor: Azul Systems, Inc.
  javaVendorVersion: Zulu11.43+100-CA
  osName: Linux
  osVersion: 5.9.11-rockchip64
  osArchitecture: aarch64
  availableProcessors: 6
  freeMemory: 67019920
  totalMemory: 442499072
bindings:
  - exec
  - mail
  - networkupstools
  - pushover
  - robonect
  - sonos
  - systeminfo
  - tellstick
  - tibber
  - zwave
clientInfo:
  device:
    ios: false
    android: false
    androidChrome: false
    desktop: true
    iphone: false
    ipod: false
    ipad: false
    edge: false
    ie: false
    firefox: true
    macos: false
    windows: true
    cordova: false
    phonegap: false
    electron: false
    nwjs: false
    webView: false
    webview: false
    standalone: false
    os: windows
    pixelRatio: 1.25
    prefersColorScheme: light
  isSecureContext: false
  locationbarVisible: true
  menubarVisible: true
  navigator:
    cookieEnabled: true
    deviceMemory: N/A
    hardwareConcurrency: 8
    language: sv-SE
    languages:
      - sv-SE
      - sv
      - en-US
      - en
    onLine: true
    platform: Win32
  screen:
    width: 1536
    height: 864
    colorDepth: 24
  support:
    touch: false
    pointerEvents: true
    observer: true
    passiveListener: true
    gestures: false
    intersectionObserver: true
  themeOptions:
    dark: light
    filled: true
    pageTransitionAnimation: default
    bars: filled
    homeNavbar: default
    homeBackground: default
    expandableCardAnimation: default
  userAgent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:83.0) Gecko/20100101
    Firefox/83.0
timestamp: 2020-12-09T12:32:19.503Z

Additional information

Using jdbc-mysql persistence.

Also see discussion at https://community.openhab.org/t/small-graph-glitches-in-oh3/109734

This issue was moved by ghys from openhab/openhab-webui#611.

[ghost]

@ghys commented on Dec 9, 2020, 3:45 PM UTC:

I'd be willing to take a bet that it's a timezone problem i.e. you live in UTC+1/2 and openHAB's JDBC service queries are made against UTC time.

[ghost]

@ghys commented on Dec 9, 2020, 3:50 PM UTC:

Try to use the developer tools in Chrome or Firefox, find the request where the data is fetched, find the last point's time, and use new Date(<the timestamp>) in the console to figure out the date, if it's consistent with what you see on the graph, then it's not an UI issue, but a persistence service issue:

[ghost]

@DanielMalmgren commented on Dec 9, 2020, 4:35 PM UTC:

Well, that value is consistent with the last point I see in the graph... I noticed that the URL used for fetching the values are "http://rockpi:8080/rest/persistence/items/Ljusniva_ute?starttime=2020-12-08T16%3A30%3A51.981Z&endtime=2020-12-09T16%3A30%3A51.980Z", while the clock (in my local timezone, which is +1) is 17:30. Does that mean the problem is on the persistence service side? The values in my database are in local time.

[ghost]

@ghys commented on Dec 9, 2020, 4:41 PM UTC:

&endtime=2020-12-09T16%3A30%3A51.980Z", while the clock (in my local timezone, which is +1) is 17:30

that's correct, note the Z at the end, 16:30 zulu time is 17:30 your time.
So the persistence service should do the conversion.

[ghost]

@DanielMalmgren commented on Dec 9, 2020, 4:44 PM UTC:

Ok. Is it possible to move this ticket over to the correct place or should I create a new one and reference this?

[DanielMalmgren]

Since this now turned out to be a persistence problem, I started thinking... My mysql connection setting is the following:

url=jdbc:mysql://127.0.0.1:3306/openhab?serverTimezone=Europe/Stockholm

Could the serverTimezone be a problem here?

[cweitkamp]

Seems to be related or similar to #8502.