For reasons I understand are complex and genuine, results of a
SELECT ... GROUP BY time() query are timestamped with the start of the time group. I use such queries in a bunch of CONTINUOUS QUERY to collect statistics on live data, something like:
CREATE CONTINUOUS QUERY cq_total_interesting ON d BEGIN SELECT count(whatever) AS total_interesting INTO d.autogen."stats" FROM d.autogen.events WHERE whatever = 'interesting' AND something <= -50 GROUP BY time(4h) fill(0) END
This collects counts of interesting events every 4 hours, but they are timestamped by the start of the 4 hour period. This is a bit odd when you go to plot or analyse the results. The most recent entry will be timestamped up to 8 hours ago, and it will be for the 4 hour period from the timestamp on. New entries will have timestamps 4 hours old. Generally my question is “how many interesting events were there in the last four hours”, for which I need to find the entry marked four hours ago.
Anyway, for this reason and several other scenarios, it would be handy to be able to time-shift the timestamp returned by a query. Something like:
SELECT time + 4h, count(whatever) FROM d.autogen.events WHERE whatever = 'interesting' AND something <= -50 GROUP BY time(4h) fill(0)
Does such functionality exist? Does it warrant a feature request? Would it be more appropriate to have a setting on the GROUP BY that specifies which timestamp to return? There already has been some discussion about the best timestamp to return, but AFAIK, none about having the option.