MySQL: Data from specific day group by year

Question

I have two columns "date" and "temperature". I want to select average temperature between two specific dates e.g. 1 - 7 April grouped by year. When i use this query

select avg(temperature) from table where dayofyear(date) between 91 and 97 group by year(date)

two problems appear.

In leap years dayofyear for days after 28 February has different value
When I want select average from e.g. between 30 December and 5 January this query: select avg(temperature) from table where dayofyear(date) between 364 and 5 group by year(date) returns 'null'.

In coresponding to point 2 is another problem. How to receive data between e.g. 30 December (a year before) and 5 January grouped by year?

KYDronePilot · Accepted Answer · 2019-04-14 16:36:07Z

1

For the first question, use the SQL DAY() and MONTH() functions:

SELECT AVG(temperature)
FROM table
WHERE DAY(date) between 1 and 7 AND MONTH(date) = 4
GROUP BY YEAR(date)

For the second part, you may have to split it into two parts.

edited Apr 14, 2019 at 16:36

answered Apr 14, 2019 at 14:21

KYDronePilot

5553 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Robson Over a year ago

That's period was only for example. When period contains two different months your query returns incorrect values.

KYDronePilot Over a year ago

Would enclosing the month and day sections in parenthesis and adding multiple of them using the ‘OR’ operator work?

Robson Over a year ago

I take the datas from some variable in my code. So I don't know when period contains one and when two months. I can use some 'IF' operations with your solution but I looking for simpler methods.

KYDronePilot Over a year ago

What exact type of data are you getting from your code? Two days of year? Two days of month and months of year?

Robson Over a year ago

Date in format 'yyyy-mm-dd'. Modify code to extracting day and month number is no problem for me

|

Collectives™ on Stack Overflow

MySQL: Data from specific day group by year

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related