Calling MySQL Gurus...

[mcrossley]

Problem, I want the total of the sunshine hours per hour summed across eternity (well all the data in my log table anyway!).

The HrsSunShine column is an incrementing value every 5/10 minutes, so I will have to take the max value in each hour and subtract the min value from it. So far so good.

Then I want to sum the sunshine per hour grouping by hour each day, across all the data in the table, ideally I also want to normalise the whole lot into a percentage.

The final output I'm looking for would look like this..

Code: Select all

Hour  SunHrs
----  ------
0      0
1      0
...
6      1278
7      23890
...
13    38763846
...
17    23764
18    1234
...
23    0

Any ideas?

[tobyspond]

Mark,

Is this what you are looking for?

select hour, sum(sun) as sun, round(sum(total),0) as total, round((sum(sun)/sum(total)) *100,1) as percent from(SELECT day(logdatetime)as day, hour(logdatetime) as hour, (max(hrssunshine)-min(hrssunshine)) as sun, count(hour(logdatetime))/12 as total FROM `name of database table` group by year(logdatetime), month(logdatetime), day(logdatetime), hour(logdatetime)) as t group by hour

note:
count(hour(logdatetime))/12

I use a 5 minute interval, so change the divisor as needed

Kerry

[mcrossley]

Wonderful , I hoped you see this Kerry

Quite quick too, well 2.5 seconds if it isn't cached, not too bad.

Now to figure out why 0.3% of my sunshine occurs at midnight

Hmm, I think the percentage is not calculating what I thought. Summing the percentages should get 100%? That query gives 265% on my data.

This sort of works using a second query of the day data table, the sum of the % comes out at 92.2 rather than 100 though...

Code: Select all

SELECT @grandTotal:=SUM(HoursSun)
FROM daydata;

SELECT 	hour, sum(sun) AS sun,
		round(sum(sun)/@grandTotal *100,1) AS percent
FROM (
	SELECT 	day(LogDateTime) AS day,
			hour(LogDateTime) AS hour,
			(max(HrsSunShine) - min(HrsSunShine)) AS sun
	FROM fulldata
	WHERE LogDateTime > '2013-4-14'
	GROUP BY year(logdatetime), month(logdatetime), day(logdatetime), hour(logdatetime)) AS t
GROUP BY hour

[tobyspond]

Mark,

"Hmm, I think the percentage is not calculating what I thought. Summing the percentages should get 100%? That query gives 265% on my data."

My version calculates the percent on each individual hour, so it will exceed 100% for the entire 24 hours. I think the query can be reworked to give you percent based on 24 hours.

"Now to figure out why 0.3% of my sunshine occurs at midnight"

it may be carry over from the previous day.

Kerry

[mcrossley]

I carry on playing with it Kerry, but my first stab is here: http://weather.wilmslowastro.com/graphs_historic.php
Sunshine -> By Hour

[tobyspond]

Mark,

I think I may have found the problem. In the query the min sun hours is subcontracted from the max sun hours by each hour, which is not giving the correct result. For example, on June 5th 2013 between 8 am and 9 am, 9 am and 10 am, 10 am and 11 am, there was sixty minutes of sunshine, which should appear as 1 in the query results. What actually shows up is 0.9. As it stands now, the minimum value during the 8 am hour is 2.3 and the maximum value is 3.2. The first value at 9 am is 3.3, so what I think the query needs to do is to subtract the previous hour from the next hour. Otherwise we are losing time and which may explain the 92.5% you are seeing.

Kerry

[mcrossley]

Mark,

I think I may have found the problem. In the query the min sun hours is subcontracted from the max sun hours by each hour, which is not giving the correct result. For example, on June 5th 2013 between 8 am and 9 am, 9 am and 10 am, 10 am and 11 am, there was sixty minutes of sunshine, which should appear as 1 in the query results. What actually shows up is 0.9. As it stands now, the minimum value during the 8 am hour is 2.3 and the maximum value is 3.2. The first value at 9 am is 3.3, so what I think the query needs to do is to subtract the previous hour from the next hour. Otherwise we are losing time and which may explain the 92.5% you are seeing.

Kerry

That makes sense, but also probably introduces another problem at midnight when the accumulated value rolls over to zero. And I won't even mention taking account of DST oops now if Steve had chosen to to use UTC internally for time stamps....

[mcrossley]

My updated query now converts everything to UTC so the hours are added properly (which is OK for the UK, anyone outside the UK will probably want to use a different TZ).

I still haven't looked at changing the calculation to "min(hour+1) - min(hour)" yet...

Code: Select all

SELECT @grandTotal:=SUM(HoursSun)
FROM daydata;

SELECT hour, sum(sun) AS sun,
       round(sum(sun)/@grandTotal *100,1) AS percent
FROM (
    SELECT day(CONVERT_TZ(LogDateTime,'EUROPE/London','UTC')) AS day,
           hour(CONVERT_TZ(LogDateTime,'EUROPE/London','UTC')) AS hour,
           (max(HrsSunShine) - min(HrsSunShine)) AS sun
    FROM fulldata
    WHERE LogDateTime > '2013-4-14'
    GROUP BY year(CONVERT_TZ(logdatetime,'EUROPE/London','UTC')), 
             month(CONVERT_TZ(logdatetime,'EUROPE/London','UTC')),
             day(CONVERT_TZ(logdatetime,'EUROPE/London','UTC')),
             hour(CONVERT_TZ(logdatetime,'EUROPE/London','UTC'))) AS t
GROUP BY hour

[mcrossley]

OK, a version that takes account of the final sunshine for an hour being logged under the next hours datetime stamp, I've cut off the hours 00:00 and 23:00 to avoid the midnight rollover when the counter reset to zero (only affects 23:00, but exclude 00:00 to keep it symmetrical and 12:00 in the centre of the graph.

Only problem is that it is now a bit slow, the execution time up from 0.15 seconds to about 3.6 seconds, so any tuning tips?

Code: Select all

SELECT @grandTotal := sum(HoursSun)
FROM daydata;

SELECT hour,
		sum(sun) AS sun,
		round(sum(sun) / @grandTotal * 100, 1) AS percent
FROM (
	SELECT hour(CONVERT_TZ(t.LogDateTime,'EUROPE/London','UTC')) AS hour,
			(min(n.HrsSunShine) - min(t.HrsSunShine)) AS sun
	FROM fulldata as t
	JOIN fulldata as n
	ON t.LogDateTime = date_sub(n.LogDateTime, INTERVAL 1 hour)
	WHERE t.LogDateTime > '2013-4-14'
	AND hour(t.LogDateTime) BETWEEN 2 AND 22
	GROUP BY date_format(t.LogDateTime, '%Y%m%d%H')
) as x
GROUP BY hour

[tobyspond]

Mark,

If you add an index field (autoincremented), you can use something like this for your base query (not tested):

select hour, sum(diff) as sun from (select mfile.id, mfile.logdatetime, hour(mfile.logdatetime)as hour, mfile.hrssunshine, min(mfile.hrssunshine)-min(b.hrssunshine)as diff from mfile left outer join mfile b on b.id = (mfile.id -12)
where year(mfile.logdatetime)>2011 group by year(mfile.logdatetime), month(mfile.logdatetime), day(mfile.logdatetime),hour) as t group by hour

change the table names and the date in the where clause. The 12 comes from the 5 minute logging time.

Regardless, an index field should speed things up.

Kerry

[mcrossley]

Thanks Kerry, I'll take a look at this when I get a moment.