Query returns 1970-01-01 messing up long term data

1

New to InfluxDB, using v1.8.

I’m collecting temperature, humidity and air pressure data and storing this in a database, using Node Red. That works fine and I get pretty pictures in Grafana.

This query returns results as expected:

SELECT ROUND(MAX(temp)*10)/10 As MaxHigh, ROUND(Mean(temp)*10)/10 As Mean, ROUND(MIN(temp)*10)/10 As MinLow FROM SensorData WHERE time >= '2021-01-17 01:00:00' AND time < '2021-01-18 01:00:00'

time                 MaxHigh Mean MinLow
----                 ------- ---- ------
2021-01-17T01:00:00Z 17.3    15.5 14.7

However, this query returns time as 1970-01-01T00:00:00Z:

SELECT ROUND(MEAN(TopDecile)*10)/10 As AvgHigh FROM (SELECT TOP(temp, 30) AS TopDecile FROM (SELECT temp FROM SensorData WHERE time >= '2021-01-17 01:00:00' AND time < '2021-01-18 01:00:00'))

time                 AvgHigh
----                 -------
1970-01-01T00:00:00Z 16.8

Same result when using time < ‘2021-01-18 00:00:00’ or time < ‘2021-01-17 23:59:59’

What I want is long term storage of Average High temp (avg low, etc.) per day, but with these queries my long term series shows this:

time                 AvgHigh AvgLow MaxHigh Mean MinLow
----                 ------- ------ ------- ---- ------
1970-01-01T00:00:00Z 16.8    14.8                
2021-01-16T01:00:00Z                16.8    14.9 13.5
2021-01-17T01:00:00Z                17.3    15.5 14.7

I expected:

time                 AvgHigh AvgLow MaxHigh Mean MinLow
----                 ------- ------ ------- ---- ------
2021-01-16T01:00:00Z 17.3    13.5    16.8    14.9 13.5
2021-01-17T01:00:00Z 16.8    14.8    17.3    15.5 14.7

What am I missing here?

2

Hello @TomJC70,
I think you need a group by in your query.
Like
GROUP BY time(1d)
Note: InfluxDB often uses epoch 0 ( 1970-01-01T00:00:00Z ) as a null timestamp equivalent. If you request a query that has no timestamp to return, such as an aggregation function with an unbounded time range, InfluxDB returns epoch 0 as the timestamp.

Does that work for you?

1 Like