As you deduced, the calendar boundaries are round numbers. Days always start at 00:00 UTC, Hours start at xx:00 UTC, minutes start at xx:xx:00 UTC, etc. Any grouping in between starts at the initial boundary and adds from there. E.g. every four hours means 00:00 UTC, 04:00 UTC, 08:00 UTC, etc.
The offset is provided to shift that boundary start, as you deduced. On Mon, Jun 13, 2016 at 6:26 PM, ian lutz <[email protected]> wrote: > 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]> 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]. >>> 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/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 > <https://groups.google.com/d/msgid/influxdb/c0ec8b23-3cd6-41a7-8035-5f86ce5a92c0%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/CALGqCvPoAtUghX1a6sT8irfitQmEDSb0PT0C_C-0HagEN0HUmg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
