MYSQL query to get dates from all years around today-CodePudding

I have a table with data points for every day of the year with a few year's history. Now I want the average of the data for the dates around today, 15 days in the past and 15 days in the future. Normally this would work with something like

select average(data_point)
from my_table
where dayofyear(point_date) between dayofyear(now()) - 15 and dayofyear(now())   15

If the current day is 01-11-2021, then this gives an average over all days from october 16 to november 16 for all years.

But this does not work when the current date is within 15 days of the new year. If the current date is 21-12-2021 then dayofyear(now()) 15 for is 370 and you only get dates from december 6th until december 31st. It should return decembr 6th until january 5th.

It will also not work when you modulo on 365 because then the dayofyear would result in between 340 and 5 and that returns no results.

Any hints how to accomplish this?

CodePudding user response：

You can get the same day as the current date in the year of the value of point_date with:

CONCAT(YEAR(point_date), '-', DATE_FORMAT(CURRENT_DATE, '%m-%d'))

with only one exception: if the current date is the 29th of February in a leap year (because this date is not valid in any year).

You can aggregate with this query:

SELECT AVG(data_point)
FROM my_table
WHERE point_date BETWEEN 
  CONCAT(YEAR(point_date), '-', DATE_FORMAT(CURRENT_DATE, '%m-%d')) - INTERVAL 15 DAY
  AND
  CONCAT(YEAR(point_date), '-', DATE_FORMAT(CURRENT_DATE, '%m-%d'))   INTERVAL 15 DAY;

If you want to take into account the exception, then use COALESCE() for DATE_FORMAT() to get the 28th of February as the vase date instead:

SELECT AVG(data_point)
FROM my_table
WHERE point_date BETWEEN 
  CONCAT(YEAR(point_date), '-', COALESCE(DATE_FORMAT(CURRENT_DATE, '%m-%d'), '02-28') - INTERVAL 15 DAY
  AND
  CONCAT(YEAR(point_date), '-', COALESCE(DATE_FORMAT(CURRENT_DATE, '%m-%d'), '02-28')   INTERVAL 15 DAY;

CodePudding user response：

This query calculates the start end endday number and add or sub this from each year. So it goes over the end of year or wrap before 1.1 of year

SELECT average(data_point)
FROM my_table
point_date BETWEEN 
    STR_TO_DATE(CONCAT(YEAR(point_date),'-01-01'),"%Y-%c-%D" )   INTERVAL (DAYOFYEAR(NOW()) -15) DAY
AND
    STR_TO_DATE(CONCAT(YEAR(point_date),'-01-01'),"%Y-%c-%D" )   INTERVAL (DAYOFYEAR(NOW())  15) DAY;