Im trying to figure out the most efficient way of calculating statistics using data from MySQL database with dates.
Currently, I use the following syntax
SELECT sum(Precipitation) from DataTable GROUP BY YEAR(Datetime)
This works perfectly fine, I get the total rainfall for each year. However, now I would like to implement the option to set the beginning of the rain season. In some places, the rain season might begin for example in September. In such case I would need the same calculation, i.e. also grouped by "years", but always since Sep to Aug.
I was thinking about how to do this and the only way I can think of would be somehow calculating the monthly sums and the using PHP try to add them up. But the problem is that that would probably be much slower given there is lots of data and the original script uses just this one line above.
Is there any more efficient way of then getting something like
2014 - xyz inches, 2015 - xyz inches, but where the 2014 would correspond for example to season 2014/2015 etc.
The data in the table is like this: column 1 is always the Datetime and then the actual value, data in 5 minute intervals. I need to maintain the table structure, so I cannot create a different table where the values would be organized differently.