In order to try and calculate a 3 month average of data as a new data field, I had to two options:
- Divide the count of data by 28 (Assuming for 28 days in a month)
- Calculate the number of days in the last 3 months, and divide the count of data by that.
While option 1 would have been far simpler, It wouldn’t really work because it wouldn’t really be a 3 month average. Even if the number was 30, or 30.42 (365 / 12), it would be better, but it still wouldn’t be as accurate as it could be. So I went with option 2, which is fairly simple when you break it down, think laterally and using the MySQL last_day() function.
For this, we need to consider that you’re never going to be able to simply work out the last day of the last 3 months, and add that together. We need to get four figures;
- The number of days in the current month so far
- The number of days in the previous month
- The number of days in the month before that
- The difference between the number of days in the month before that, and the date exactly 3 months ago
Then we can simply add those four figures together, and we have the number of days in the past 3 months. Put that logic together with some SQL, and I came up with this:
NOW() AS 'now',
DATE_SUB(NOW(), INTERVAL 3 MONTH) AS '3m ago',
@dayCount4 := DATE_FORMAT(NOW(), '%d') AS 'Days this month so far',
@dayCount3 := DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '%d') AS 'Days last month',
@dayCount2 := DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), '%d') AS 'Days month before last',
@dayCount1 := DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 3 MONTH)), '%d') - DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH), '%d') AS 'Days in month before that',
@dayCount1 + @dayCount2 + @dayCount3 + @dayCount4 AS totalDays
Note: Running the SQL above won’t give you this exact result set – I’ve altered the query to run as multiple Unioned queries simply to produce an easier to read screenshot
And now, you know how to calculate the number of days in the past 3 months. This logic can obviously be extended to whatever time period you need to use it for.
Or, if you use the search term ‘get days between dates mysql‘ instead of ‘get days in last 3 months mysql‘ when googling how to do this, the first result would tell you there’s already a MySQL function called datediff() that already does all this for you. Or even dayofyear() for now and the date 3 months ago, and calculate the difference. Or even timestampdiff() and tell it you want the difference in months. But It’s always good to think laterally about problems to find your own solution every now and then.