Wrong time(zone) with JDBC persistence and Postgres

Tutorials & Examples
1

I tried to visualize my weather data with Grafana. Unfortunately all times were shifted by 2 hours . It took me some hours tho figure out what was wrong and how to solve it. This tread is primarily for information, so other people can save that effort.

Problem: Obviously Openhab connects to the Postgres with local timezone and save the with the database function “now()”. But the time field doesn’t contain a timezone. If you read that time with a non-Openhab-Client it may get interpreted as UTC (in my case 2 hours difference).

(You could switch to Grafana to UTC explicitly to see the “correct time number”, but the time filters can not be deceived.)

Be aware, that the timezone is a session parameter and you may have to configure you SQL client properly to see your local time. For example Jetbrains Datagrip connects with UTC by default.

Solution

Change time column type from “timestamp” (without time zone!) to “ timestamp with time zone”.

alter table <itemtable> alter column time type timestamp with time zone using time at time zone 'CEST';

Open points

  • Unfortunately I could NOT convince OpenHab to create new tables with that timezone type, even it should be configurable via “sqltype.TABLEPRIMARYKEY“.
  • I couldn‘t find the source code for the current OpenHab addon for checking the docu. Where can I find it? (I only found the code for OpenHab V1 addons.)
2

I have changed your thread topic to “Tutorials & Examples”
Thank you for your contribution

3

@raulix I might be wrong but it seems to me that the persistence addons don’t have a separate repo for openHAB2. This https://github.com/openhab/openhab1-addons/tree/master/bundles/persistence/org.openhab.persistence.jdbc/java/org/openhab/persistence/jdbc looks like the code I have in my 2.x setup.

4

@raulix this should be possible with sqltype.TABLEPRIMARYKEY config option (see: https://www.openhab.org/addons/persistence/jdbc/)… but there is a bug (see: https://github.com/openhab/openhab1-addons/pull/5820) which makes this option useless for now.

5

If the change linked above is merged, the fix for this problem will be: sqltype.tablePrimaryKey=TIMESTAMP WITH TIME ZONE line added to /etc/openhab2/services/jdbc.cfg

6

@skazi Thanks. Let’s see when the change gets through.

7

skazi’s changes were just merged.

Altering the tables I would prefer
alter table <itemtable> alter column time type timestamp with time zone using time at time zone 'Europe/Amsterdam';
with the region not the abbreviation because for daylightsaving time, 2 offsets will be used.