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:

`SELECT`

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

Running this SQL today gives us the following result set;

*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.