InfluxDB Difference time range problem

Add-ons Persistence Services
1

Hello all,

I’m using InfluxDB with Grafana to work out the daily differences between various values, eg: electricity and gas meter readings at the beginning and end of a day. I have the following query:

SELECT difference(last(“value”)) FROM “MEMT1” WHERE $timeFilter GROUP BY time(1d) fill(null)

However the result of this query is a value summed from 1:00am - 00:59 am rather than from 0:00am to 23:59. (See image) I cannot figure out how to force the query to calculate the difference from midnight to midnight. Is this a timezone problem? If so, how can I control the timezone setting? Or is it something else?

Thanks in advance.

BTW, I originally posted this on the Influx community, but it seems pretty dead over there…

Difference issue
Difference issue1108×472 29.8 KB

2

Hi,

tbh. I don’t think it’s a timezone “issue”, rather than influxDB telling you “the data from hour ‘0’ is the ‘first hour of the day’, so I’m putting a 01:00:00” there.

Could you try the following;

SELECT difference(last(“value”)) from "MEMT1"
WHERE time >= 'YYYY-MM-DDT00:00:00'
AND time < 'YYYY-MM-DDT00:00:00'
GROUP BY time(1d)
3

Thanks for the suggestion. But doesn’t this result in a fixed period query? I’ll try it out to see if it delivers the correct results, but if it is a fixed period it won’t be the final solution.

4

Oh, one other thing. The calculation the Influx makes starts with data from 01:00, not from 00:00. So it’s shifted in time by one hour. Specifically, the following openhab rule calculates the correct value for a given day (offset):

energy = (MEMT1.historicState(now.withTimeAtStartOfDay.minusDays(offset)).state as Number) - (MEMT1.historicState(now.withTimeAtStartOfDay.minusDays(offset+1)).state as Number)

The figures that the latter calculation returns correspond with the figures that I get from the meter itself.

5

No, I just wanted to verify if the results are the same. I know that your query has

WHERE $timeFilter

in it :wink:

6

Hi Chris, I tried variations on this query:

SELECT difference(last(“value”)) FROM “MEMT1” WHERE time >= ‘2020-03-01T00:00:00’ AND time < ‘2020-03-03T23:59:59’ GROUP BY time(1d)

But I get "no data to show’…

7

How are you using InfluxDB? If you use it via Docker make sure that all containers are using the same / the host timezone. Details see in this post: InfluxDB+Grafana persistence and graphing

1 Like
8

Hi Felix, I’m not familiar with Docker - ie I haven’t explicitly used it to install InfluxDB.

9

Than don’t worry, if you don’t know Docker you haven’t used it :wink:

10

I guessed that might be the case… :sweat_smile:

1 Like
11

Hi there,
that’s interesting - you could put a ‘Z’ at the end of your times (like 2020-03-01T00:00:00Z). Normally you’d do this if your time is in UTC and you don’t want to have an offset (we’re basically trying now to see if you really don’t have a TZ issue).

If you’d like to dig into, there’s an ISO 8601 specification on this.

A few examples of difference() are here, but I’d need to read into that as I’m not using this function.