Hi
I would like to do something like this:
SELECT last(temperature) as FROM temperatures WHERE time > ‘2017-01-01’ GROUP BY time(1d)
however per day I would like to have the last temperature before a certain time, say 18:00. Can I do that somehow in InfluxDB?
sbains
August 14, 2017, 3:06pm
2
Hi David,
I suppose you are looking for (24 hrs) of data starting at 18:00(yesterday) - 18:00(today). The offset function will be useful in achieving this; you can define the query according to your TZ, by introducing time offset like below:
SELECT last(temperature) FROM temperatures WHERE time > ‘2017-01-01’ GROUP BY time(1d, -5h)
Note: This query will need modification with any TZ changes (e.g. Daylight etc)
sbains
August 14, 2017, 3:21pm
3
Actually if you look at this feature
influxdata:master ← influxdata:js-6541-timezone-support
opened 02:05AM - 25 Dec 16 UTC
The timezone for a query can now be added to the end with something like
`TZ('A… merica/Los_Angeles')` and it will localize the results of the
query to be in that timezone. The offset will automatically be set to
the offset for that timezone and offsets will automatically adjust for
daylight savings time so grouping by a day will result in a 25 hour day
once a year and a 23 hour day another day of the year.
The automatic adjustment of intervals for timezone offsets changing will
only happen if the group by period is greater than the timezone offset
would be. That means grouping by an hour or less will not be affected by
daylight savings time, but a 2 hour or 1 day interval will be.
The default timezone is UTC and existing queries are unaffected by this
change.
When times are returned as strings (when `epoch=1` is not used), the
results will be returned using the requested timezone format in RFC3339
format.
Fixes #6541.
A combination of this and offset should work well in your case.
That is great, thanks so much!
