Sean - thanks so much for this reply, super helpful! For the time being we've gone with an AWS Lambda that fires every minute and executes a query with an INTO clause, simulating a CQ. Works pretty well so far!
Thanks again, -Avery On Thursday, June 16, 2016 at 4:48:23 PM UTC-4, Sean Beckett wrote: > > That is correct. RESAMPLE EVERY 1m ... GROUP BY time(24h) means every > minute recalculate the data for the 24 hours that start at midnight and > include today. > > What you want, which is to calculate the 24h query every minute for the > previous 24h -- NOT the previous calendar day -- is not supported by a > single CQ. The system automatically buckets on round number boundaries and > they are not relative to the time at which the query is run. > > There is an offset to shift the bucket boundaries > <https://docs.influxdata.com/influxdb/v0.13/query_language/data_exploration/#group-by-time-intervals>, > > but it, too, is absolute, not relative. > > One way to accomplish what you want right now is with 1440 configured CQs, > each running every minute. It's a heavy load on the system and a poor > workaround, but it will work. It should be easy to script their creation, > but basically you want something like this (omitting the syntax that > doesn't matter): > > CREATE CQ foo1 RESAMPLE EVERY 1m BEGIN > SELECT ... GROUP BY time(24h) > END > > CREATE CQ foo2 RESAMPLE EVERY 1m BEGIN > SELECT ... GROUP BY time(24h, 1m) > END > > CREATE CQ foo3 RESAMPLE EVERY 1m BEGIN > SELECT ... GROUP BY time(24h, 2m) > END > > ... > > CREATE CQ foo1440 RESAMPLE EVERY 1m BEGIN > SELECT ... GROUP BY time(24h, 1439m) > END > > Every minute, the system will recalculate the 24h value for all 1440 > possible 24h buckets. > > Another way to solve the problem would be to automate an ad hoc query. Use > the -execute > <https://docs.influxdata.com/influxdb/v0.13/tools/shell/#execute-an-influxql-command-and-quit-with-execute> > > flag for the CLI and submit, every minute, the appropriate query: SELECT > ... GROUP BY time(24h, Xm), where X would monotonically increase to 1439 > until resetting to 0, each day. > > On Tue, Jun 14, 2016 at 1:10 PM, Mathias Herberts <[email protected] > <javascript:>> wrote: > >> IIRC CQs are evaluated on intervals which are separated by the 'GROUP BY' >> timespan and fall on round boundaries, so basically you are recomputing >> every minute the data for the current 24hour interval. >> >> On Tuesday, June 14, 2016 at 8:21:10 PM UTC+2, [email protected] wrote: >>> >>> Hey Folks, >>> >>> We are trying to run a CQ each minute which calculates a bunch of >>> aggregates for the last 24 hours on a sliding scale. We are using: >>> >>> RESAMPLE EVERY 1m ....... GROUP BY time(24h) >>> >>> >>> The problem is we are getting our resulting time stamps as >>> "2016-06-14T00:00:00Z" >>> (for example) which gives us one measurement *per day* instead of one >>> measurement *per minute* for the last 24 hours. >>> >>> >>> Would appreciate any direction on how to do this correctly! >>> >>> >>> Thanks, >>> >>> -Avery >>> >> -- >> Remember to include the InfluxDB version number with all issue reports >> --- >> You received this message because you are subscribed to the Google Groups >> "InfluxDB" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> Visit this group at https://groups.google.com/group/influxdb. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/influxdb/58185324-0bd5-44d6-999f-c33b0101d4f7%40googlegroups.com >> >> <https://groups.google.com/d/msgid/influxdb/58185324-0bd5-44d6-999f-c33b0101d4f7%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> For more options, visit https://groups.google.com/d/optout. >> > > > > -- > Sean Beckett > Director of Support and Professional Services > InfluxDB > -- Remember to include the InfluxDB version number with all issue reports --- You received this message because you are subscribed to the Google Groups "InfluxDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/influxdb. To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/2f77ec44-4d3e-4d95-a666-2bf6e8d5e65c%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
