Grafana + MariaDB help with time offset

Third party integrations
1

Hi everybody,

I am currently happily runnig Hass.io on my RaspberryPi Zero W.
Because I like graphs, i switched to MariaDB as my database so I can create some dashboards with Grafana. The Pi Zero does not support InfluxDB and i use Grafana in the Cloud.

Now the only problem I’m facing is that in Grafana, the values from all entities are -02:00 hours off. While it is 3pm in Berlin, the current values are displayed at 1pm.
When i use Squirrel to access my MariaDB, i used commands like

SELECT CURRENT_TIME;

I get the correct time of 3pm

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
I get 02:00 as a result, which is the correct offset.

But how can i tell Grafana to also use the correct time zone?
I tried playing with the time zone settings in Grafana (setting it to UCT, default, local-browser-time) but only changing it to UCT made a difference (offsetting even further).

This is an example of my temperature sensor in Grafana:

SELECT
  $__timeGroupAlias(last_updated,1m),
  avg(state) AS "Temp Wohzimmer"
FROM states
WHERE
  entity_id = 'sensor.temperature_wohnzimmer'
GROUP BY 1
ORDER BY $__timeGroup(last_updated,1m)

Hass.io in the meantime displays graphs correctly without any offset.

What can i do to tell Grafana that the time values should all be shiftet 2 hours?
Can i somehow create a new column in my MariaDB that tells Grafana the shifted hours, without messing up the Graphs in Hass.io?

Any help is welcome :slight_smile:

Greetings from Berlin

1 Like
2

any solution?

3

Hi, i could not change the time zone. I ended up buying a Rpi3b+ and running InfluxDB

4

Probably it’s late but hope this helps someone else. The following query is working fine for me:

SELECT CONVERT_TZ(last_changed,’+00:00’,’+02:00’) AS “time”, entity_id, state AS ‘Consumption’ FROM states WHERE entity_id = ‘sensor.shelly_shem_d3b38e_2_current_consumption’ ORDER BY state_id desc