Some common SQL Queries that you might like...

Timestamp is a default field in the database - but trying to convert the timezones on it would be problematic - my code is for MariaDB on the RPI - other sql versions use commands like CONVERT_TZ - so should be able to find out how to get it working with Grafna…


I experimented some more with my SQL and found a few instances where it could return the wrong value... It could find the maximum value in the subselect but in order to get the time in the main select it could 'somehow' return an incorrect value - a time from a different date... So it needed another clause on the where statement to restrict it to the same date range.

So I give you an updated listing of what I have - and remember these are checking for data that is available on the run day of today - if you run this before midnight each day, it ill give you the highs lows etc for that day... But if it runs just after midnight its only going to look at data for that day - which might be none or very little....

I have decided that I am going to be running this at 7:30am each morning - to display the data for the previous day. So every Date(Now()) field (and there is 2 in most of the SQL statements will need to change to include a '- timedelta(1)' or something so if you run it on a Friday its going to use Thursdays date in the SQL...

So here are the revised SQL;

Max Temperature
SELECT `outsideTemperature`, TIME_FORMAT(Time(CONVERT_TZ(TimeStamp,'+00:00','+10:00')),'%h:%i %p') FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW()) and `outsideTemperature` in (SELECT Max(`outsideTemperature`) FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW())) LIMIT 1

Min Temperature
SELECT `outsideTemperature`, TIME_FORMAT(Time(CONVERT_TZ(TimeStamp,'+00:00','+10:00')),'%h:%i %p') As LocalTm FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW()) and `outsideTemperature` in (SELECT Min(`outsideTemperature`) FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW())) LIMIT 1

Daily Rainfall
SELECT MAX(`totalRain`) FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW())

Maximum Gust of Wind
query = "SELECT `currentWindGust`, TIME_FORMAT(Time(CONVERT_TZ(TimeStamp,'+00:00','+10:00')),'%h:%i %p') FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW()) and `currentWindGust` in (SELECT Max(`currentWindGust`) FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW())) LIMIT 1

Lightning Count for the Day
query = "SELECT `as3935LightningCount` FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW()) and `as3935LightningCount` in (SELECT Max(`as3935LightningCount`) FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW())) LIMIT 1

Lightning Distance - closest for the day
query = "SELECT MIN(`as3935LastDistance`) FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW()) AND `as3935LastDistance`> 0 LIMIT 1

Lightning Distance Furthest for the Day
SELECT MAX(`as3935LastDistance`) FROM `WeatherData` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW()) AND `as3935LastDistance`> 0 LIMIT 1

Lightning Strike - Last one
query = "SELECT DATE_FORMAT(CONVERT_TZ(Max(TimeStamp),'+00:00','+10:00'),'%a %d %b %Y'), TIME_FORMAT(Time(CONVERT_TZ(Max(TimeStamp),'+00:00','+10:00')),'%h:%i %p') FROM `WeatherData` where `as3935LightningCount` > 0 LIMIT 1

Maxiumum UV Today
SELECT `UVIndex`, TIME_FORMAT(Time(CONVERT_TZ(TimeStamp,'+00:00','+10:00')),'%h:%i %p') FROM `Sunlight` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW()) and `UVIndex` in (SELECT Max(`UVIndex`) FROM `Sunlight` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW())) LIMIT 1

Average UV Today (only looking at daylight values ie > 0)
SELECT Avg(`UVIndex`) FROM `Sunlight` WHERE Date(CONVERT_TZ(TimeStamp,'+00:00','+10:00')) = Date(NOW()) And `UVIndex` > 0