0

I have a bunch of temperature sensors in my house. I poll the sensors every minute and store the data in a mysql database that looks like this:

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| property   | varchar(50)  | YES  | MUL | NULL    |       |
| devloc     | varchar(50)  | YES  | MUL | NULL    |       |
| sensortype | varchar(50)  | YES  | MUL | NULL    |       |
| timest     | datetime     | YES  | MUL | NULL    |       |
| value      | decimal(8,4) | NO   | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+

For any given polling cycle, I sample 17 sensors, each one with a unique "devloc". The timestamp given to a particular sample is the same for all 17 entries. A given sample run can be reported like this:

mysql> select * from sample where date(timest) = curdate() and 
       property='myhouse' order by timest desc limit 17;
+-----------+---------------------+------------+---------------------+---------+
| property  | devloc              | sensortype | timest              | value   |
+-----------+---------------------+------------+---------------------+---------+
| myhouse   | garage_ext1         | temp       | 2021-01-29 00:19:01 | 34.4750 |
| myhouse   | hvac_attic_supply   | temp       | 2021-01-29 00:19:01 | 73.5125 |
| myhouse   | hvac_attic_return   | temp       | 2021-01-29 00:19:01 | 70.9250 |
| myhouse   | hvac_main_supply    | temp       | 2021-01-29 00:19:01 | 79.7000 |
| myhouse   | hvac_main_return    | temp       | 2021-01-29 00:19:01 | 66.6500 |
| myhouse   | attic_side          | temp       | 2021-01-29 00:19:01 | 69.8000 |
| myhouse   | attic_main          | temp       | 2021-01-29 00:19:01 | 69.2375 |
| myhouse   | upstairs_right_bed  | temp       | 2021-01-29 00:19:01 | 76.5500 |
| myhouse   | upstairs_middle_bed | temp       | 2021-01-29 00:19:01 | 70.3625 |
| myhouse   | upstairs_guest_bed  | temp       | 2021-01-29 00:19:01 | 72.6125 |
| myhouse   | garage_main         | temp       | 2021-01-29 00:19:01 | 54.9500 |
| myhouse   | basement            | temp       | 2021-01-29 00:19:01 | 63.6125 |
| myhouse   | study               | temp       | 2021-01-29 00:19:01 | 71.1500 |
| myhouse   | master_bed          | temp       | 2021-01-29 00:19:01 | 69.4625 |
| myhouse   | kitchen             | temp       | 2021-01-29 00:19:01 | 69.3500 |
| myhouse   | den                 | temp       | 2021-01-29 00:19:01 | 69.6875 |
| myhouse   | outside             | temp       | 2021-01-29 00:17:52 | 24.3500 |
+-----------+---------------------+------------+---------------------+---------+

I would like to report on 3 specific devloc values for a given date (or date range). I want to see outside, hvac_main_return and hvac_main_supply on a single line, for each sample interval. Something like this:

+---------------------+---------+------------------+------------------+----------+
| timest              | outside | hvac_main_return | hvac_main_supply | property |
+---------------------+---------+------------------+------------------+----------+
| 2021-01-29 00:19:01 | 24.3500 | 66.6500          | 79.7000          | myhouse  |
| 2021-01-29 00:20:01 | 24.1000 | 71.5200          | 87.6125          | myhouse  |
... etc ... etc...
+---------------------+---------+------------------+------------------+----------+

I can write simple queries, but this one is out of my league. I have limited database/SQL knowledge.

Any help would be appreciated. Thank you

0

I think I found my answer at (https://stackoverflow.com/questions/8283541/merging-multiple-rows-into-one-row-and-multiple-columns-on-mysql).

A little modification and I came up with this:

select x.timest,     
    max(case when x.devloc='outside' then x.value end) as outside,     
    max(case when x.devloc='hvac_main_return' then x.value end) as hvac_main_return,     
    max(case when x.devloc='hvac_main_supply' then x.value end) as hvac_main_supply     
from sample x where date(timest) = curdate() 
group by timest     
order by timest desc;

It gives me output like this:

+---------------------+---------+------------------+------------------+
| timest              | outside | hvac_main_return | hvac_main_supply |
+---------------------+---------+------------------+------------------+
| 2021-01-28 23:59:54 | 24.8000 |          67.4375 |          82.9625 |
| 2021-01-28 23:58:45 | 24.9125 |          67.1000 |          80.8250 |
| 2021-01-28 23:57:42 | 24.9125 |          66.0875 |          78.2375 |
| 2021-01-28 23:56:33 | 24.9125 |          64.9625 |          74.8625 |
| 2021-01-28 23:55:32 | 25.0250 |          62.3750 |          73.0625 |
| 2021-01-28 23:54:17 | 25.0250 |          62.8250 |          74.7500 |
+---------------------+---------+------------------+------------------+

And I can tweak the where date(timest) = curdate() to give me a specific date or date range.

Does anyone know how I can add the difference between hvac_main_return and hvac_main_supply as another column?

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.