2

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.

  1. In leap years dayofyear for days after 28 February has different value
  2. 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?

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.

  • That's period was only for example. When period contains two different months your query returns incorrect values. – Robson Apr 14 at 14:31
  • Would enclosing the month and day sections in parenthesis and adding multiple of them using the ‘OR’ operator work? – KYDronePilot Apr 14 at 14:38
  • 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. – Robson Apr 14 at 18:39
  • What exact type of data are you getting from your code? Two days of year? Two days of month and months of year? – KYDronePilot Apr 14 at 18:43
  • Date in format 'yyyy-mm-dd'. Modify code to extracting day and month number is no problem for me – Robson Apr 15 at 4:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.