Count recurring records on data base every 30 minutes interval

Question

I need some help on the below

i have the table "Data" getting data stored on it like the below

+------------+----------+-----------
| regdate    | regtime  | items              
+------------+----------+-----------
| 2013-03-02 | 09:12:03 | item1   
| 2013-03-02 | 10:12:05 | item1          
| 2013-03-02 | 15:12:07 | item2    
| 2013-03-02 | 20:12:09 | item3       
| 2013-03-02 | 21:12:11 | item4    
| 2013-03-02 | 22:12:14 | item3

and so on for 10 items can go to the table at different times during the day from 09:00 till 23:00

and i have another table "ItemsPerInterval" like the below

+------------+-------------+-------------+-------------+-------------+
| regdate    | reginterval | item1       | item2       | item3       |
+------------+-------------+-------------+-------------+-------------+
| 2013-03-01 | 09:00:00    | 0           | 0           | 0           |
+------------+-------------+-------------+-------------+-------------+

and so on where the interval is increasing on 30 minutes basis like 09:00 , 09:30 , 10:00, 10:30 ... till 23:00

What i want to do is to insert data on the table ItemsPerInterval to include the recurring count for the items on the table "Data" (item1, item2 ,item3 ,.... item10) and store them upon interval basis (09:00:00, 09:30:00, 10:00:00, 10:30:00, .... 23:00:00) so that each interval - for example - 09:00:00 will have the recurring count for all items that has been registered on the Data table from 09:00:00 till 09:29:59 and so one for the rest of intervals up till 23:00:00

EDIT **1

so eventually all below intervals must be registered on the ItemsPerInterval table and hold the count for each item of the 10 items that has been reported with in the interval time window

09:00:00
09:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00
13:00:00
13:30:00
and so on .....
.
.
.
22:00:00
22:30:00
23:00:00

/EDIT **1

i did some readings about routines / procedures but i don't know the difference and don't know how to use it to implement the above.

i am using Software: MySQL Software version: 5.5.24 with PHP.

Appreciate your support

Thank you

Are there `regtime` outside the 09:00-23:00 scope and if so, how should those be handled? — Wrikken, Mar 12 '13 at 22:12
storing the results in a new table doesn't make much sense, because you can always retrieve this data from table data using a query that will select and group items per interval. — michi, Mar 12 '13 at 22:12
And you round to half hour is probably something like `FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(regtime)/1800)*1800);` — Wrikken, Mar 12 '13 at 22:14
@Wrikken yes by human mistake some items will be registered out side this scope , but i will add later another table to track out of scope items. so for now on this one consider out of scope items to be ignored — Ahmed ElGamil, Mar 12 '13 at 22:16
@michi i don't want the end user page to request any queries from the DB for stability issues. that's why i prefer to dumb the data to one table first and execute one query later to just extract it from the perinterval table — Ahmed ElGamil, Mar 12 '13 at 22:18
@AhmedElGamilI if you want non-existing intervals (nothing happened), the data has to come from _somewhere_ for a `LEFT JOIN`, so you; ll probably have to create a dummy table with the `09:00,09:30....22:30,23:00` sequence. — Wrikken, Mar 12 '13 at 22:52
about the out of scope items , on the below solution i added the whole day intervals from 00:00:00 till 23:30:00 to the intervals table , now you can manage and have a full view on the whole day — Ahmed ElGamil, Mar 14 '13 at 15:50

fthiella · Accepted Answer · 2013-03-14T14:44:42.787

You could use this query:

SELECT
  regdate,
  SEC_TO_TIME(TRUNCATE(TIME_TO_SEC(regtime)/(60*30),0)*(60*30)) reginterval,
  COUNT(CASE WHEN items='item1' THEN 1 END) item1,
  COUNT(CASE WHEN items='item2' THEN 1 END) item2,
  COUNT(CASE WHEN items='item3' THEN 1 END) item3,
  COUNT(CASE WHEN items='item4' THEN 1 END) item4
FROM Data
GROUP BY
  regdate,
  reginterval

If you need to insert the resulting rows into a new table, just add this line at the beginning:

INSERT INTO ItemsPerInterval (regdate, reginterval, item1, item2, item3, item4)
SELECT ...

Edit

If you also want to show all intervals, even if they have no values, I think that the best you can do is to have a table Intervals that contains all intervals that you need:

CREATE TABLE Intervals (
  reginterval time
);

INSERT INTO Intervals VALUES
('09:00:00'),
('09:30:00'),
('10:00:00'),
...

This query returns all intervals, combined with all dates present in your table:

SELECT Dates.regdate, Intervals.reginterval
FROM
  (SELECT DISTINCT regdate FROM Data) Dates,
  Intervals

And this query returns the rows you need:

SELECT
  di.regdate,
  di.reginterval,
  COUNT(CASE WHEN Data.items='item1' THEN 1 END) item1,
  COUNT(CASE WHEN Data.items='item2' THEN 1 END) item2,
  COUNT(CASE WHEN Data.items='item3' THEN 1 END) item3,
  COUNT(CASE WHEN Data.items='item4' THEN 1 END) item4
FROM (
  SELECT Dates.regdate, Intervals.reginterval
  FROM (SELECT DISTINCT regdate FROM Data) Dates,
       Intervals
  ) di
  LEFT JOIN Data
   ON di.regdate=Data.regdate
      AND di.reginterval=SEC_TO_TIME(TRUNCATE(TIME_TO_SEC(Data.regtime)/(60*30),0)*(60*30))
GROUP BY
  regdate,
  reginterval;

Please see fiddle here.

How does this work

The idea here is to convert regtime, which is a field that contains a time, to the number of seconds since the beginning of the day, using TIME_TO_SEC function:

TIME_TO_SEC(regtime)

we then divide this number to 60*30 which is the number of second in 30 minutes, keeping only the integer part:

TRUNCATE(number_of_seconds/(60*30), 0)

then we multiply the integer part back to 60*30 to obtain the number of seconds, rounded by 60*30 seconds (30 minutes).

with SEC_TO_TIME we convert the number of seconds back to a time field.

Thank you very much for this answer, but lets assume nothing has been registered for example on the the interval 13:00:00 , i still want this to be visible showing 0 for all the data -- see topic edit please — Ahmed ElGamil, Mar 12 '13 at 22:47
@AhmedElGamil i updated my answer, it will show all 0 when nothing has been registered on an interval — fthiella, Mar 13 '13 at 09:11
Thank you very much it is working. this saved me a lot of run time that could have been done by other queries. — Ahmed ElGamil, Mar 13 '13 at 16:12
aahm ,, well update, while live testing the count is not correct . for example | 2013-03-02 | 09:12:03 | item1 | 2013-03-02 | 09:28:05 | item1 the count should reflect on the first interval 2 , but its showing 1 one 9 and one on 9:30 — Ahmed ElGamil, Mar 14 '13 at 01:49
@AhmedElGamil you probably need a TRUNCATE(..,0) instead of ROUND(), i updated again — fthiella, Mar 14 '13 at 08:07
yep its working now :) thank you , if you dont mind can you explain for me the work of this one ? SEC_TO_TIME(TRUNCATE(TIME_TO_SEC(regtime)/(60*30),0)*(60*30)) reginterval, — Ahmed ElGamil, Mar 14 '13 at 11:19
ahm just one more thing please , this code is working fine when i take it and apply it over sql query on my phpadmin , but it doesn't work when i add it into an event to run every 30 minutes , any idea why is that ? thank you again in advance — Ahmed ElGamil, Mar 15 '13 at 07:35

Count recurring records on data base every 30 minutes interval

1 Answers1