MYSQL group by with dynamic where

Question

I have table where wind speed is inserted every couple of minutes. Lets say i would then like to calculate average wind speed for certain time window for each day.

I could use SQL command like

select ROUND(avg(data.wind),1) wind  
FROM data 
WHERE station in(109) 
&&  hour(data.datum)>=7 
&& hour(data.datum)<= 8 
group by month(data.datum), day(data.datum)

This works fine but the problem is i would like to use dynamic time slot based on sunrise time. I found SQL function to calculate sunrise time for a specific date but i dont know how to use dynamic time window for each day in group by in my sample. For sunrise i would use https://github.com/curzon01/mysql_SunRiseSet which uses command like

select SunRiseSet(yyyy-mm-dd, 45.299, 13.571, 'nautical', 'rise');

For example i would like range from sunrise - 1 hour upto sunrise + 1 hour. This would mean

day1 7:20-8:20
day2 7:21-8:21
day3 7:23-8:23
etc.

Is this possible in one command?

Bill Karwin · Accepted Answer · 2022-10-18 13:28:38Z

1

SELECT ROUND(AVG(data.wind), 1) AS wind  
FROM data 
WHERE station IN (109) 
AND TIME_TO_SEC(TIMEDIFF(TIME(data.datum), SunRiseSet(data.datum, ...))) 
  BETWEEN -3600 AND 3600 
GROUP BY MONTH(data.datum), DAY(data.datum);

I assume you can get the coordinates of the respective station to put in where I have left .... Maybe join to another table where you store the station details like location?

answered Oct 18, 2022 at 13:28

Bill Karwin

567k87 gold badges710 silver badges872 bronze badges

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

Collectives™ on Stack Overflow

MYSQL group by with dynamic where

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related