user2370078 user2370078 - 1 year ago 69
MySQL Question

MySQL grouping by season

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.

Answer Source

Use this query:

SELECT SUM(Precipitation)
FROM DataTable

This query shifts every date backwards by 8 months, with the result that September 1, 2016 would appear to be the first day of 2016, and August, 2016, would appear to be the last month of 2015.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download