JDBC/TimescaleDB (postgresql) - Timestamps not UTC? Bug?

Add-ons Persistence Services
, ,
1

Hi,

I’m comparing graphs in Grafana from 2 different sources (influxdb and timescaledb/postgresql) but for some reason all data in the timescaleDB is ahead 1 hour.
I’m not sure but shouldn’t jdbc timestamp all data as UTC by default? If I read this debug correct it is saved with timezone.

2022-02-17 13:25:59.006 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO tableName=my_item_value; newTableName=null;
2022-02-17 13:25:59.010 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item my_item_value
2022-02-17 13:25:59.011 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'my_item_value' as Type 'NUMBERITEM' in 'my_item_value_1' with state '4127'
2022-02-17 13:25:59.012 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '4127'
2022-02-17 13:25:59.014 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE PRECISION; javaType=class java.lang.Double;
2022-02-17 13:25:59.015 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '4127.0'
2022-02-17 13:25:59.016 [DEBUG] [ersistence.jdbc.db.JdbcPostgresqlDAO] - JDBC::doStoreItemValue sql=INSERT INTO my_item_value_1 (TIME, VALUE) VALUES( NOW(), CAST( ? as DOUBLE PRECISION) ) value='4127.0'
2022-02-17 13:25:59.020 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'my_item_value' as '4127' in SQL database at Thu Feb 17 13:25:59 CET 2022 in 16 ms.

When the debug was run my LOCALTIME (UTC+1) was 13:25:59, shouldn’t it be stored as 12:25:59?

Here I’m debug’ing influxdb 10 minutes later, where it’s stored in UTC:
2022-02-17 13:34:02.548 [TRACE] [.influxdb.InfluxDBPersistenceService] - Storing item my_item_value(Type=NumberItem, State=2145, Label=Totalt effekt forbruk, Category=null, Groups=[gPersistInfluxChange, gPersistTimescaleDBChange, gThreeHourStateCheck, gWatt]) in InfluxDB point InfluxPoint{measurementName='my_item_value', time=2022-02-17T12:34:02.548438Z, value=2145, tags={item=my_item_value}}

2

It doesn’t say what timestamp it was stored with; it’s telling you when it did the storing (in local time, using the same clock the logger is using)

3

Good point but does ‘now()’ mean UTC or with timezone? When i check the db manually (psql) i always get the data utc+1, even when I set the session to UTC :thinking:

4

Don’t know, that will be psql(?) idea of now.

What does that look like exactly?
If the db actually stores timezone info, it doesn’t need to work with UTC. That’s a convention used when there is only a zoneless timestamp available - 13:00 is ambiguous unless you can guarantee the zone i.e. always UTC. 13:00+0 and 12:00+1 are unambiguously the same instant, because they come along with their timezone info.

A quick glance at PostgreSQL notes -

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone.

suggests that what you get depends on what PostgreSQL thinks is the local zone… but it should come with the necessary zone info, I think.

5

So I did a manual lookup through psql on the value stored in my example above:

openhab=# select * from w_huset_alle_1 where value  = 4127 order by time desc limit 10;
            time            | value
----------------------------+-------
 2022-02-17 13:27:44.003187 |  4127
 2022-02-17 13:27:22.547905 |  4127
 2022-02-17 13:26:18.975989 |  4127
 2022-02-17 13:25:59.020208 |  4127 <--------- This one
 2022-02-17 13:20:26.5027   |  4127

openhab=# show timezone;
 TimeZone
----------
 UTC
(1 row)

So I tried setting the timezone manually by:

openhab#set timezone='Europe/Oslo'
openhab=#show timezone;
  TimeZone
-------------
 Europe/Oslo
(1 row)

But the query returns the exact same timestamp…
2022-02-17 13:25:59.020208 | 4127

Edit:
Also added some queries:

openhab=# SELECT LOCALTIMESTAMP AT TIME ZONE 'localtime';
           timezone
-------------------------------
 2022-02-17 15:28:20.021353+01
(1 row)

openhab=# set timezone='UTC';
SET
openhab=# SELECT LOCALTIMESTAMP AT TIME ZONE 'localtime';
           timezone
-------------------------------
 2022-02-17 13:28:43.340895+00
(1 row)

So this means setting the timezone in the cli should have an effect (AFAIK) …?

6

I don’t know where PostgreSQL looks to determine the timezone it should report in.

What actual problems is this causing you? I don’t think openHAB cares, it should sort it all out internally in its own local timezone.

7

Hm, the problem is when I try to visualize the data externally, ie. Grafana. It’s actually wrong when using the TimescaleDB and the only way to solve it is to manipulate the data (timechanging hack).
Basically I can’t compare sources since the timeline is not equal and that seems weird, to timestamp differently on different db’s`?

here’s an output in grafana visualizing the problem (this is the same item and timeperiod, and the bottom one is the actual correct time):

image
image1787×793 75 KB

8

Dunno if this helps

or more promising