Data returned outside of time range specified

1

I am trying to return data grouped roughly into months. The query I have specified I think should return data between Jan 1, 2018 and December 31, 2018 grouped roughly by month. Here is the query I am using:

SELECT sum(“main_dwh”)-sum(“solar_dwh”) FROM “btmonnew”.“autogen”.“energynew” WHERE time >= 1514782800000ms and time <= 1546318799000ms GROUP BY time(730h) fill(null)

Here are the results

time sum_sum
12/19/17 07:00:00PM 549375.3161074469
1/19/18 05:00:00AM 899900.1586062482
2/18/18 03:00:00PM 915118.138338136
3/21/18 02:00:00AM 839070.6602808374
4/20/18 12:00:00PM 802537.0391738953
5/20/18 10:00:00PM 933986.1733281822
6/20/18 08:00:00AM 1265013.7455643143
7/20/18 06:00:00PM 1305740.5819446738
8/20/18 04:00:00AM 1154249.7036127779
9/19/18 02:00:00PM 939143.3997122133
10/20/18 12:00:00AM 891143.7938968358
11/19/18 09:00:00AM 834780.4375310587
12/19/18 07:00:00PM 325519.4516609162

What I don’t understand is why I am getting data returned from December of 2017. It doesn’t seem to make difference weather I group by minutes, hours or days.

3

This looks like it could be a time zone issue. Are you using an offset?

4

The exact query I am using is in the original post, and there is no offset. If it helps I am in Eastern standard time (-5 GMT), but I am not sure how or why that would return data from December 19th. The timestamps in the query refer to local time (-5gmt), so I guess if anything I might be seeing 5 hours of data from Jan 2019.

5

@adamalli I see that. I didn’t realize that first date was in 2017. Your instinct to check the group_by was right. It seems related to this issue regarding group_by on GitHub.

6

First off …you query contains GROUP BY time(730h), which is like 30.41 days :slight_smile: So maybe you want to have it 720h? …however even with that …
But more importantly…influxdb does NOT natively support grouping by one month as per issue 3991
And even more importantly, you realize that not every month has 720 hours, right?
January (30 days) has indeed 720 hours
February (28/29 days) has 672/696 hours
April (31 days) month has 744 hours
And because of this fact, the data is being shifted back and forth and that’s why the output is so weird.

Therefore using “GROUP BY time(720h)” is NOT the same as you would group it by 1 month

7

Yes I know Influxdb can’t group by month, hence why I am doing it by 730h as that is the best we can do right now. And I know this won’t get me actual calendar months, but I don’t really have a better option. If you take 8760 hours and divide by 12 you get 730h, so it is a rough approximation.

That still doesn’t explain why I am getting data returned before my start date. The time interval I picked with the grouping should fit evenly. So with the math above I should get exactly 12 data points returned all within my selected time period. Instead I get 13 data points returned including the first point starting before my selected time period. And it isn’t off by just a little, it is almost 12 days, so not a timezone problem.