Grouping records hour by hour or day by day and filling gaps with zero or null in mysql

Question

I have written a query that counts records hour by hour:

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'),count(*) from req group by
TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24');

the result is:

2012-02-22 13    2280
2012-02-22 15    1250
2012-02-22 16    1245
2012-02-22 19    1258

But I need a result like this:

2012-02-22 13    2280
2012-02-22 14    0
2012-02-22 15    1250
2012-02-22 16    1245
2012-02-22 17    0
2012-02-22 18    0
2012-02-22 19    1258

Also I have these queries that group by day and month too!

select TO_CHAR(copied_timestamp, 'YYYY-MM-DD'),count(*)  from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD');

select TO_CHAR(copied_timestamp, 'YYYY-MM'),count(*)  from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM');

I need their gaps to be filled with zero or null too. Any help is really appreciated.

Note: There is an answer for this question in oracle using CONNECT BY but i need the answer in Mysql because Mysql does not support CONNECT BY. Here is the link

Does the table have `24` records per day, representing each hour of the day? — Ravinder Reddy, Jun 30 '12 at 13:48
No actually, it can have more records. maybe 5 or more records per second! — Mehdi, Jun 30 '12 at 15:52
The only purpose of the `CONNECT BY` and `LEVEL` in the referenced question is to generate rows. It is an idiom in Oracle which should have an equivalent in Mysql which does the same thing. Perhaps this would help: http://stackoverflow.com/questions/1806484/generate-many-rows-with-mysql — Adam Hawkes, Jul 01 '12 at 23:03
I saw your reference. but I could not generate rows with dates, as their data! if I could generate a date range using the method mentioned in your link, I think I could handle that. — Mehdi, Jul 02 '12 at 04:02
If you need an answer in MySQL, why do you insist on the `oracle` tag? (You put it back after it was removed.) — Andriy M, Jul 03 '12 at 09:01
@Andriy Because this way oracle tags followers will be informed too. They are sql experts and might have a workaround on this issue without `connect by`. — Mehdi, Jul 03 '12 at 18:18
@Heidarzadeh You might have just insulted a few non-Oracle SQL experts out there. — shawnt00, Mar 25 '16 at 16:27

score 2 · Answer 1 · answered Jul 02 '12 at 23:51

Generate a single-column dates_hours table which contains all dates and hours within a reasonable range (e.g. from 1900 to 2200). Then do a LEFT JOIN from this table to your current query.

For this technique to perform correctly, you will probably need to add an indexed column to your table which contains a converted time stamp (your copied_timestamp converted to DATETIME, rounded to the hour)

SELECT date_hour, count(req.converted_timestamp)
FROM
    dates_hours 
    LEFT JOIN req ON req.converted_timestamp = dates_hours.date_hour
WHERE date_hour
    BETWEEN (SELECT MIN(req.converted_timestamp) FROM req)
    AND (SELECT MAX(req.converted_timestamp) FROM req)
GROUP BY date_hour

To generate the dates_hours table:

CREATE TABLE dates_hours (date_hour DATETIME PRIMARY KEY);

DELIMITER $$$
CREATE PROCEDURE generate_dates_hours (to_date DATETIME)
BEGIN

    DECLARE start_date DATETIME;
    DECLARE inc INT;

    SELECT MAX(date_hour) INTO start_date FROM dates_hours;
    IF start_date IS NULL THEN
        SET start_date = '1900-01-01';
    END IF;
    SET inc = 1;
    WHILE start_date + INTERVAL inc HOUR  <= to_date DO
        INSERT INTO dates_hours VALUE (start_date + INTERVAL inc HOUR);
        SET inc = inc +1;
    END WHILE;

END $$$
DELIMITER ;

CALL generate_dates_hours('2200-01-01');

Okay, now that I am proof-reading myself, I realise this is quite far-fetched a solution. I hope someone comes up with a more elegant one.

Thanks Yak. this solves the problem but for my usecase it is a little hard to maintain the code. I have found another solution and post it here. eager to know your idea. — Mehdi, Jul 03 '12 at 08:30

score 2 · Accepted Answer · edited Mar 25 '16 at 16:18

I created a table called TBL_NUMBERS

CREATE TABLE `TBL_NUMBER` (`n` int(11) NOT NULL)

and inserted records from 1 to 1000. Now I can generate any kind of date range using this query:

SELECT '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] or as dateRange
 FROM TBL_NUMBER
WHERE '2012-06-21' + INTERVAL n-1 [DAY | HOUR | MINUTE] <= '2012-06-23';

Then I can join this table with my results to fill the date gap. If i need more than 1000 date range I can insert more records in TBL_NUMBER

If you have any better idea, I'm eager to know that ;)

Grouping records hour by hour or day by day and filling gaps with zero or null in mysql

2 Answers2

Linked

Related