Device to Database: Arduino to MQTT to SQL

I made a minimal change in my code from the last assignment so that it would send data to the class server every 2 minutes, rather than every 10 seconds. I kept this interval consistent for light, humidity, and temperature because I’m collecting data from my living room, which stays pretty consistent across those variables over time.

For the SQL related queries of this assignment, we had to answer the following questions by querying Don’s postgres database (questions & answers below):

1. When did the outside sensor break and stop sending data?

SELECT measurement, recorded_at FROM sensor_data 
WHERE device='outside' 
ORDER BY 2 DESC LIMIT 5;

2. Show the min and max temperature in the root cellar by year.

SELECT EXTRACT(YEAR FROM recorded_at) as year, min(reading), max(reading) FROM sensor_data 
WHERE measurement = 'temperature' AND device='rootcellar' 
GROUP BY 1;

3. What was the lowest temperature recorded 2018?

SELECT EXTRACT(YEAR FROM recorded_at) as year, min(reading) FROM sensor_data 
WHERE measurement = 'temperature' AND EXTRACT(year FROM recorded_at) = '2018'
GROUP BY 1;

Challenge: Which sensor recorded the lowest temperature 2018 and when? Hint: you need a subquery.

SELECT device, reading, recorded_at
FROM sensor data
WHERE reading =
	(SELECT min(reading) FROM sensor_data
		WHERE measurement = 'temperature' AND EXTRACT(year FROM recorded_at) = '2018');

For Part 3, we had to write queries that used data from our own sensors. I tend to complain that the air in my apartment is way too dry, and I suspected that the humidity at home would be less than that of ITP.

SELECT AVG(reading) FROM sensor_data 
WHERE device = 'device_rk' AND measurement = 'humidity' AND recorded_at BETWEEN '2019-02-23' AND '2019-02-24';

SELECT AVG(reading) FROM sensor_data 
WHERE device = 'device_rk' AND measurement = 'humidity' AND recorded_at BETWEEN '2019-02-11' AND '2019-02-22';

Goes to show what I know! The first query seen above is solely measurements taken from my apartment (whereas the second set is from school), and it seems that my apartment is marginally more humid than school. I haven’t checked out the error specs of the temperature/humidity sensor we are using, but concievably this difference is within the error rate of the sensor, and the comparison is meaningless.