CODEWITHSUNDEEP
phpmysqldategrouping
Jachym
Jachym

Reputation: 485

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

Example:

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.

Upvotes: 2

Views: 501

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Use this query:

SELECT SUM(Precipitation)
FROM DataTable
GROUP BY YEAR(DATE_SUB(Datetime, INTERVAL 8 MONTH))

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.

Upvotes: 4

Related Questions