I am running into a problem when grouping by time interval. The last value of this sum seems to be incorrect and always considerably lower than what it should be. My conclusion is that the grouping performs the aggregation on time t over the time frame [t, t+n], where n is the grouping interval. I would think it more logical that the aggregation at time t would be over [t, t-n].
Attached below is a figure where the bars show the raw data, and the line shows the sum over a period of 10s. Note the last data point in the line is only half of those before, even though there was no change in the source data (bars). I changed the sum to count, to see how many data points it counted. Evidently this should be 10, but shows the same behaviour where the last one is only 5. This is also very clear in the first datapoint, which mentions 6 counts but the data point is at the start of the first bar.
My queries are listed below.
SELECT “num_msg” FROM “one_week”.“downlink_msg” WHERE $timeFilter
SELECT sum(“num_msg”) FROM “one_week”.“downlink_msg” WHERE $timeFilter GROUP BY time(10s)
SELECT count(“num_msg”) FROM “one_week”.“downlink_msg” WHERE $timeFilter GROUP BY time(10s)