I am struggling here on some quite simple queries on my influx DB. every minute, a measurement from a sensor is written into the DB.
I i try to query a certain day, e.g from 00:00:00 to 23:59:59 the starting time of the query results is not 00:01:00 as I would expect, unfortunately it is 01:01:00
The epoch time value is e.g. 1578182460000000000
If i convert that value into human readable format I get (epochconverter.com):
1578182460000000000
Supports Unix timestamps in seconds, milliseconds, microseconds and nanoseconds. Assuming that this timestamp is in nanoseconds (1 billionth of a second): GMT: Sunday, 5. January 2020 00:01:00 Your time zone: Sonntag, 5. Jänner 2020 01:01:00 GMT+01:00
What is wrong? The program which is writing values into the db and the system which is reading the values out of the database are both in the same timezone. (Europe/Vienna GMT+1)
the query is:
**> SELECT * FROM generalhistory WHERE time > '2020-01-05T00:00:00Z' and time < '2020-01-06' and DPName = 'Aussenbereich.Sensor.Hum' order by time asc limit 1;**
name: generalhistory
time DPName ID Manager Timestamp Value_Numeric Value_String
---- ------ -- ------- --------- ------------- ------------
1578182460000000000 Aussenbereich.Sensor.Hum 30104823 IPDriver_4 2020-01-05\ 01:01:00 99.9
Looking forward BR Dieter
Its quite right, the problem just when using local time (GMT+1) as filter arguments.
Therefore to get the right UTC time (GMT+0) that equal to local time (GMT+1), than the time filter must be deducted with one hour,
And the query would be following:
SELECT * FROM generalhistory
WHERE time >= '2020-01-05T00:00:00Z' -1h
and time < '2020-01-06T00:00:00Z' -1h
and DPName = 'Aussenbereich.Sensor.Hum'
ORDER BY time ASC
LIMIT 1;
Hope my answer could help.