Shift timestamp returned by SELECT query

1

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.

2

Hey @Heath_Raftery

There is an open feature request for this on the InfluxDB repo influxdb#8213. Unfortunately there isn’t a way to achieve this using InfluxDB alone. However, the behavior you’ve described is actually the default functionality of Kapacitor.

The following tickscript

batch
    |query('''
SELECT whatever
FROM d.autogen.events
WHERE whatever = 'interesting' AND something <= -50
''')
      .period(4h)
      .every(4h)
      .align()
   |count('whatever')
     .as('total_interesting')
   |influxDBOut()
     .database('d')
     .retentionPolicy('autogen')
     .measurement('stats')

will do what you’re looking for.

3

Great response, thank you!

I’d love to go the Kapacitor route instead - much easier to edit and maintain the scripts. Unfortunately, after many, many hours of experimentation and discovering of bugs, I’ve come to understand that Kapacitor is no good for updating historical data. Say I improve my query and discover the interesting stuff is <= -49, not <= -50. With CQs, I can run the query without the CQ wrapper and it’ll run on all my data, overwriting historical entries. With Kapacitor scripts, attempting to record/replay is a minefield of unexpected behaviour (see, for example, Kapacitor record query does not return same points as line protocol query · Issue #1294 · influxdata/kapacitor · GitHub).