mysql group by morning, afternoon and evening

This article comes from  codeday  By codeday
I am trying to get the log data from the table. I choose the place of time span ("maandag" to "vrijdag"). This can get the result I want, but now I get the problem that only three measurements are displayed every day

The first one is the first one after 8 o'clock
The second is the first one after 14:00 o clock
The third is the first one after 20:00

My problem is that the measurement time is not constant, so using regular expressions to select a day doesn't work
In the following query, I sometimes get 2 or 4 or more results every day. This is because the time between the two measurements is basically 2 hours, but it is not constant, so the measurement of the next day is carried out at the same time

Can I group by part of the day?
Or what other solution?

data:
id datumTijd ph tbc temperatuur vbc gbc
1 2012-10-03 14:59:19 7.30.08 24.91 0.02 0     
1 2012-10-03 16:47:38 7.33.07 22.15 0.12 0
3 2012-10-03 17:03:06 7.31.09 23.23 0.05 0


query:
SELECT N.name, D.datumTijd, ROUND(D.gbc,2), ROUND(D.vbc,2), ROUND(D.tbc,2),ROUND(D.ph,2), ROUND(D.temperatuur,1)  
FROM METING.Name N, METING.Data D 
Where D.datumTijd BETWEEN "'+maandag+' 00:00:00" AND "'+vrijdag+' 23:59:59"  
AND N.id = D.id AND D.datumTijd 
AND D.datumTijd REGEXP "0[78]:..:..|1[34]:..:..|2[12]:..:.."';
ORDER BY N.name, D.datumTijd 

Best answer

You can do this by dividing the results into three different periods. Then, you can use aggregate functions (SUM,MIN,MAX) on metrics

SELECT   DATE(D.datumTijd),
         CASE WHEN TIME(D.datumTijd) BETWEEN '08:00:00' AND '14:00:00' THEN 1
              WHEN TIME(D.datumTijd) BETWEEN '14:00:00' AND '20:00:00' THEN 2
              WHEN TIME(D.datumTijd) BETWEEN '20:00:00' AND '24:00:00' THEN 3
         END as period
...
GROUP BY DATE(D.datumTijd),
         CASE WHEN TIME(D.datumTijd) BETWEEN '08:00:00' AND '14:00:00' THEN 1
              WHEN TIME(D.datumTijd) BETWEEN '14:00:00' AND '20:00:00' THEN 2
              WHEN TIME(D.datumTijd) BETWEEN '20:00:00' AND '24:00:00' THEN 3
         END

Tags: Database

Posted on Thu, 07 May 2020 11:12:09 -0400 by david_s0