Timezone issues with Grafana and MainUI Graphs from JDBC/Postgres DB

Setup, Configuration and Use Installation
, ,
1
  • Platform information:
    • Hardware: Celeron® CPU J3455/4GB RAM/ > 100 GB SSD
    • OS: Debian GNU/Linux 10 (buster) / Docker
    • Java Runtime Environment: Java 11, Whatever comes with the Docker Image
    • openHAB version: openhab/openhab:3.0.0

I am using Grafana to display various Graphs in Openhab. I always had/have the problem that the timestamps displayed in Grafana does not match the real time. (Analyse bellow) Since I spent the last 2-3 week updating my installation I decided to have a deeper look in this issue and tried to fix it.

I figured out that the issue is, that Openhab stores the timestamps without any timezone information in the database, but Grafana assumes that the timestamps are UTC. Reading the documentation for the persistance configuration and a similar problem in the forum, I thought I can come over this issue.
I ended up with the following configuration in services/jdbc.cfg:

    url=jdbc:postgresql://postgres:5432/openhab
    user=openhab
    password=dbpass
    sqltype.tablePrimaryKey=TIMESTAMP WITH TIME ZONE

I migrated all my existing itemXXXX tables with to the new format:

alter table <itemtable> alter column time type timestamp with time zone using time at time zone 'Europe/Berlin';

After that, I thought I overcome the problem:

  • Since Openhab uses now() for inserting rows into the database they are inserted timezone aware.
  • Grafana now have timestamps in UTC format and the graphs are displayed correctly.

I few days later I realized that the graphs of the new MainUI are off by 1h. After some thoughts, I think the Graphs in the MainUI do not consider the timestamp awareness of the database.
I would like to use Graphs from both sources (Grafana and MainUI). After several hours/days of testing I feel stuck between the 2 formats and don’t see a easy solution to that.

Some Ideas:

  • Openhab uses timezone aware timestamp format: Requires changes in the Openhab implementation. Imo this would be the cleanest solution, but may break a lot of different things. I am not able to do that.
  • Somhow make the jdbc configuration aware of the timezone Backend: Also requires changes in the Openhab implementation
  • Some kind of SQL-View to provide both timezone formats, to provide Grafana a timezone aware table.

This MAY correlate with following [jdbc] Consider local timezone to create time filter by cweitkamp · Pull Request #9445 · openhab/openhab-addons · GitHub

Does somebody have the same issues and solved it somehow? Does somebody see another approach to use Grafana and MainUI Graphs?

1 Like
2

After some thinking an testing I found a solution for myself and would like to share it.

I started with the condition to NOT do a special configuration in OpenHab, since OpenHab is not very configurable at the database level. Especially I removed the configuration of sqltype.tablePrimaryKey=TIMESTAMP WITH TIME ZONE in the JDBC configuration.

If you only what the solution in Grafana scroll down to "Conclusion"

A saw 2 Solutions:

1.) Add some special database configuration, like a SQL-View that automatically translate the timezone. I steped back from that because I saw no easy sollution to automatically add additional views when adding a new item.

2.) Somehow make Grafana aware of the different timezone. Unfortunetly Grafana have the strong assumption that the time values are stored as UTC. (which is not the case using OpenHab). But I gave it a try.

Grafana uses Marcos to fill in SQL queries when drawing a graphs. After some reading I found a way build a query and macros to draw the correct graph.

Here are my steps to solve the issues:

1.) Unmodified graph

image
image2011×505 61.5 KB

In this graph the value is off by one hour. You can let Grafana create your query automatically through the Grafana querybuilder that looked like that:

image
image729×276 15 KB

After that you can have a look at the actual query that is generated, by “Edit SQL” manually:

image

2.) Moving query result into the correct timezone

I looked up some SQL documentation. For PostgreSQL there are commands to translate the result data to a different timezone. I added another query to the graph and tested it. This resulted in following query and graph:

image

image
image2005×467 83.5 KB

As you can see the new yellow graph is now moved by one hour (to the “left”). This was the correct result and I was quite happy. :slight_smile:

Unfortunetly you may realize the missing 1 hour at the end. This is caused, because the query input parameter for the “WHERE”-clause is still using the wrong timezone, so the result set is missing the last hour.

3.) Fixing the “WHERE”-clause

To fix the “WHERE” clause I had to look to the used macro you may already realized: $__timeFilter("time"). Grafana have detailed documentation (by clicking “show help”) what you can use here. After some testing I concluded in replacing this macro by "time" between $__timeFrom() and $__timeTo() at time zone 'Europe/Berlin'

So the final query and graph was:

image
image832×167 4.74 KB

image
image1976×483 83.6 KB

CHECKMATE! :slight_smile: :slight_smile: :slight_smile: :checkered_flag:

Conclusion

If I (or you :wink: ) Want to overcome the problem of Grafana graphs are displayed correctly you have to:

  1. Build your graph normally
  2. Edit the graph SQL query by:
    a. Replace the time SELECT parameter "time" with time at time zone 'Europe/Berlin' as "time"
    b. Replace the time WHERE macro $__timeFilter("time") with "time" between $__timeFrom() and $__timeTo() at time zone 'Europe/Berlin'

I use my target time zone “Europe/Berlin”, which is currently off by 1 hour from UTC. You may change it to your time zone. :wink:

I hope this helps somebody else. If somebody come up with another solution I would be happy so hear it.

1 Like
3

This is a very neat solution indeed - nice work.

But unfortunately I have close to a hundred different graphs in Grafana and it’ll be quite some work to edit all of them manually. Being Grafana, I suppose it can be automated in some way - but it’s still very cumbersome to do such a huge workaround to fix something that ought to be fixed in OpenHAB itself.

4

True. I only have about 10 graphs that I integrated from Grafana. So for me this was ok.
I currently think about picking up the “view” solution and spend some time to evaluate it again. But this is mainly for fun and play around with the data. :grin:

Do you have another solution for you or do just live with this offset?

5

As always my setup is different - but similar. Until recently I used InfluxDB and was quite happy about it; but at the same time I have a strong desire to have everything running in Kubernetes and build a high availability (HA) solution.

My home Kubernetes cluster runs on three nodes giving basic HA. The nodes run a MariaDB Galera cluster, a GlusterFS storage cluster, and Kubernetes. This has proven to be very efficient, and I can bring down one of the nodes at anytime for maintenance like upgrades without disturbing most apps running in the cluster.

Unfortunately InfluxDB cannot be setup for HA without a very expensive subscription - and that’s out of the scope for my home cluster. Hence I tried moving the database to MariaDB now that I already have it running for other purposes. And then I hit the timezone problem.

I haven’t decided what to do yet - except that I’m not going to pay for an expensive InfluxDB HA solution. :slight_smile:

6

I just played around with my database and I think using views that convert the time table to a UTC table will work fine.

Following view look promising and I think I will build back my Grafana and use the views. So there are no changes required in Openhab and Grafana

CREATE OR REPLACE VIEW public.utc_item0358 
AS
  SELECT 
    timezone('Europe/Berlin'::text, item0358."time") AS "time",
    item0358.value
   FROM item0358
  ORDER BY item0358."time" DESC;

You have to add a view for each table, but I think this can be done with a simple script.

7

This would require changes in every kind of database that openHAB can use via persistence.
The current solution is to persist everything in UTC by design, conversion is carried out by persistence service when persisting/retrieving data. All invisible to OH users.
Your problem arises when you access the database outside of persistence service e.g.with Grafana.
It’s Grafana that needs to be timezone aware; as most users don’t complain about this, I guess there’s a setting somewhere.