SELECT only 9:00 and 21:00 data per day, from mySQL table-CodePudding

I have a mySql table which receives data in every hour from a smart scale (only one scale/table), so there is 24 row each day. The Mysql table contains 3 column: timestamp: 2022-02-01 20:00:27 (only for example), Weight (Float), Temp(float).

I have a query to see only 21:00 data from each day:

SELECT
  DATE(timestamp),
  Weight as Weight21,
  Temp as Temp21
FROM table_name
WHERE HOUR(timestamp) >= 21
GROUP BY DATE(timestamp)

this will show me the following:

Date       |  Weight21  |   Temp21   |
--------------------------------------
2022-01-01 |     23,1   |     16,0   |  
2022-01-02 |     24,4   |     17,5   |   
2022-01-03 |     25,3   |     12,3   |  
2022-01-04 |     26,2   |     10,4   |  
2022-01-05 |     27,0   |     15,8   |

Now, i would like to make a query to see both 9:00 and 21:00 data from each day, but mySql don't let me to use multiple WHERE cluase or multiple SELECT. The other problem is, that it can happen, that some day have no 9:00 or 21:00 data, or both can missing. In this case would be good to write NULL or something. So i want a query to make something like this:


Date       |   Weight9   |   Weight21   |   Temp9   |   Temp21  |
-----------------------------------------------------------------
2022-01-01 |     23,1    |     27,2     |   12.1    |    16.3   |
2022-01-02 |     24,3    |     28,4     |   13.6    |    15.2   |
2022-01-03 |     NULL    |     29,7     |   NULL    |    12.7   |
2022-01-04 |     26,5    |     30,3     |   12.1    |    14.3   |
2022-01-05 |     27,7    |     31,6     |   11.0    |    16.4   |

CodePudding user response：

For you first question correct statement you want to use is 'OR' and not another 'WHERE'.

If you want to see booth results from 9:00 and 21:00 your WHERE should be like this:

WHERE HOUR(timestamp) = 21 OR HOUR(timestamp) = 9

Now to your second question i'm afraid you can't do it with just one table that store your data. My best way to do this is in your backend to send this date/day as null.

CodePudding user response：

I think this should cover your requirements:

SELECT
  DATE(timestamp),
  MAX(CASE WHEN HOUR(timestamp)=9 THEN Weight END) as Weight9,
  MAX(CASE WHEN HOUR(timestamp)=21 THEN Weight END) as Weight21,
  MAX(CASE WHEN HOUR(timestamp)=9 THEN Temp END) as Temp9,
  MAX(CASE WHEN HOUR(timestamp)=21 THEN Temp END) as Temp21
FROM table_name
WHERE HOUR(timestamp) IN (9,21)
GROUP BY DATE(timestamp);

You can do WHERE HOUR(timestamp) IN (9,21) or WHERE HOUR(timestamp)=9 OR HOUR(timestamp)=21 but with the query above, you might not need it at all. Unless the data is too big. Also, this is using existing date data therefore any date/time not present is the data will not be returned. If you want all running dates returned as well, you need to do a LEFT JOIN with a generated date/ or calendar table - if you have one; which I do recommend you create for your own use especially when you're using older MySQL version.

See other variations in this demo fiddle