Excellent thanks!!
I think, I have managed to do what I wanted not without a little bit of faffary. I could not deduce exactly from the doc, where to "calendar" boundaries really are. I want to know the boundary before I make the query so i can calculate the offset and get the results I want: This is what I have done (start time= 1448928000000ms) influx db default start time for group interval = ((start time from epoch 0)/(group interval)) * (group interval) //using integer/long devision removes remainder (this is the calendar start time) offset = startTime - resultFrom above = 449280000ms final query: SELECT mean(value_EIGAI), count(value_EIGAI) FROM "egtesta-c1" WHERE "time" >= 1448928000000ms AND "time" < 1457913600000ms GROUP BY "time"(898560000ms, 449280000ms) And I get what I want... :) Some other tests I have indicate it holds for other queries / data... Thanks! On Thursday, 9 June 2016 03:50:07 UTC+10, Sean Beckett wrote: > > > https://docs.influxdata.com/influxdb/v0.13/troubleshooting/frequently_encountered_issues/#understanding-the-time-intervals-returned-from-group-by-time-queries > > and > https://docs.influxdata.com/influxdb/v0.13/query_language/data_exploration/#group-by-time-intervals > > should clear up the confusion. Note that you need 0.13 to configure GROUP > BY time boundaries. > > On Tue, Jun 7, 2016 at 11:04 PM, ian lutz <[email protected] <javascript:>> > wrote: > >> Also this is very strange: >> >> > SELECT count(value_EIGAI) FROM "egtesta-c1" WHERE "time" >= >> 1448928000000ms AND "time" < 1454367600000ms GROUP BY "time"(5439600000ms) >> ORDER BY DESC >> name: egtesta-c1 >> ---------------- >> time count >> 1452373200000000000 531 >> 1446933600000000000 213 >> >> Why should there be two results? >> I cannot get a count back in one value, that represents the number of >> data points in the given interval >> >> >> On Wednesday, 8 June 2016 05:26:21 UTC+1, ian lutz wrote: >>> >>> Hmm >>> >>> looks like thre is bug on v12.1 >>> SELECT mean(value_EIGAI), count(value_EIGAI) FROM "egtesta-c1" WHERE >>> "time" >= 1448928000000ms AND "time" < 1457913600000ms GROUP BY >>> "time"(898560000ms) ORDER BY DESC >>> >>> produces less results (the final 4 0 entries above are missing) >>> >>> on v13 all results are sent back (well...as above). >>> >>> >>> >>> On Wednesday, 8 June 2016 03:18:49 UTC+1, ian lutz wrote: >>>> >>>> Hi All >>>> >>>> Given; the following please observe the query and returned timestamps >>>> >>>> >>>> > SELECT mean(value_EIGAI), count(value_EIGAI) FROM "egtesta-c1" WHERE >>>> "time" >= 1448928000000ms AND "time" < 1457913600000ms GROUP BY >>>> "time"(898560000ms) >>>> name: egtesta-c1 >>>> ---------------- >>>> time mean count >>>> 1448478720000000000 0 >>>> 1449377280000000000 0 >>>> 1450275840000000000 0 >>>> 1451174400000000000 -5.653622902418279 130 >>>> 1452072960000000000 2.0144539870301656 250 >>>> 1452971520000000000 -0.5222659899082899 249 >>>> 1453870080000000000 -2.529149156078376 115 >>>> 1454768640000000000 0 >>>> 1455667200000000000 0 >>>> 1456565760000000000 0 >>>> 1457464320000000000 0 >>>> >>>> >>>> I am asking for 10 evenly spaced intervals (groups) over which to get a >>>> count for each (you will note that (1457913600000-1448928000000)/ >>>> 898560000) = 10): >>>> >>>> Odd things: >>>> >>>> 1. The first timestamp is before the '"time" >= 1448928000000ms' >>>> timestamp; before the where clause's timestamp >>>> 2. there are 11 returned values; >>>> >>>> >>>> Questions >>>> >>>> where are the absolute bounds of the time ranges that are being >>>> aggregated in this groupby statement ? it looks like its >>>> >>>> Start1 - 1448478720000000000 >>>> End1 - 1449377280000000000 >>>> (no points inbetween, difference is 898560000, as per group by >>>> statement) >>>> >>>> >>>> Start1 - 1449377280000000000 >>>> End1 - 1450275840000000000 >>>> (no points inbetween, difference is 898560000, as per group by >>>> statement) >>>> >>>> ETC ETC >>>> >>>> So it looks like influx chooses the first time to start the aggregates >>>> in some way unknown to me then groups up over intervals bases on that >>>> starting point; >>>> >>>> >>>> How does influx db choose the first time stamp, why is not >>>> 1448928000000ms (the limit in the where claus) ??? >>>> Will datapoints between 1448478720000000000 (start of returned first >>>> group) and 1448928000000ms be included, even though they are outside the >>>> bounds (in this example there are no points in the raw data)? >>>> Is this expected behaviour ? >>>> >>>> Thanks very much! >>>> Ian >>>> >>>> >>>> >>>> >>>> -- >> 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/2b247c2e-72bb-42f3-85bb-625514430d93%40googlegroups.com >> >> <https://groups.google.com/d/msgid/influxdb/2b247c2e-72bb-42f3-85bb-625514430d93%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/c0ec8b23-3cd6-41a7-8035-5f86ce5a92c0%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
