Excellent workaround! That's effectively the same as my proposed CLI solution but you've automated it in a more maintainable way.
On Thu, Jun 16, 2016 at 3:23 PM, <[email protected]> wrote: > 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]> >> 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]. >>> 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/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 > <https://groups.google.com/d/msgid/influxdb/2f77ec44-4d3e-4d95-a666-2bf6e8d5e65c%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/CALGqCvMW4vDE6L%2BuvXrOTPcqEbJ_pWCVcXPV_mbQLk-ga6%3D5FA%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
