We have a lot of data with a lot of events with a lot of dates in a MySQL db. Generating lists of e.g. events per year, or students per year, is fairly simple. Now, we've decided to change everything to "school year", that is e.g. the school year 2022-23 or 2024-25. For example, we're currently doing something like
SELECT COUNT(*) FROM events GROUP BY YEAR(startdate)
I can hardly imagine how we would do this in a simple manner, if all years now run from July 1st to June 30th.
How in the world would I do something like this in MySQL?
You can still use the YEAR()-function by adding 7 months to the date:
SELECT concat(YEAR(startdate +interval +7 month)-1,'-',SUBSTR(YEAR(startdate +interval +7 month),-2)) as schoolyear,
COUNT(*)
FROM events
GROUP BY schoolyear;
If you use that in many queries, you can make a function out of that and use it:
create function f_schoolyear( in_date date )
returns char(7)
deterministic
begin
return concat(YEAR(in_date +interval +7 month)-1,'-',SUBSTR(YEAR(in_date +interval +7 month),-2));
end
Usage:
SELECT f_schoolyear(startdate) as schoolyear, COUNT(*)
FROM events
GROUP BY schoolyear;
See a dbfiddle.