MySQL output hours in a day

toastyman · 18 Aug 2009 at 21:57

Evening all!

I'm after a bit of advice with a query I'm writing as I'm not sure if it's possible..

I want to return a list of numbers representing the hour in the day, showing the value of the record if it exists and 0 if it doesn't.

Table (stats_pagetraffic):
page_id | timestamp | count
1 | 2009-08-18 13:00:00 | 1
1 | 2009-08-18 16:00:00 | 1
1 | 2009-08-18 17:00:00 | 5
1 | 2009-08-18 18:00:00 | 2

Query so far:

Code:

SELECT GROUP_CONCAT(`stats_pagetraffic`.`count`) AS `pagetraffic` FROM `stats_pagetraffic` WHERE `stats_pagetraffic`.`page_id`='1' AND `stats_pagetraffic`.`timestamp`>='20090818000000' AND `stats_pagetraffic`.`timestamp`<='20090818230000' ORDER BY `stats_pagetraffic`.`timestamp` ASC

Currently returns
pagetraffic = 1,1,5,2

I want it to return
pagetraffic = 0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,1,5,2,0,0,0,0,0
where the 0's are hours that don't exist in the stats_pagetraffic table

Any ideas?

toastyman · 19 Aug 2009 at 09:55

I'm wondering if it can be done by having a subquery which lists the timestamps for 0am to 23pm. You could then do some sort of join to lookup the dates in the statistics table. The problem there is I have no idea how to go about writing that subquery to list the timestamps!

I'm using PHP but i'm quite resistant against using it for this as it's not just going to return data for one page.. it'll be for about 20. That's why i'd like to do as much of it as possible in the database in as few queries as possible.

toastyman · 19 Aug 2009 at 20:18

Fantastic, did the trick nicely! Just got to remember to have all the hours in the other table..

Here's the final query..

Code:

 SELECT bob.timestamp, Max( bob.count ) AS pagetraffic
FROM (
SELECT timestamp, 0 AS count
FROM stats_hours
UNION
SELECT s.timestamp, s.count
FROM stats_pagetraffic s
WHERE s.page_id =61
AND s.timestamp >= '20090819000000'
AND s.timestamp <= '20090819230000'
) AS bob
GROUP BY bob.timestamp
ORDER BY bob.timestamp ASC

Thanks to you both!

Next step is to get it working with GROUP_CONCAT.. when I put GROUP_CONCAT(Max(bob.count)) it gives an 'Invalid use of group function' error. Perhaps I'm implementing it wrong..

toastyman · 20 Aug 2009 at 20:31

I quite liked the bob bit which is why I kept it! Absolutely stumped on the group_concat.. struggling to try and find a workaround

toastyman · 21 Aug 2009 at 13:29

That last one worked great, thanks very much!

Just to be a real pain i've created another small problem. I've taken that query and added it to my other query.. the theory with this one is that it wouldn't just return the records for one page but would now return the records for lots of pages.

Code:

SELECT `sec`.`name`,`sec`.`published`,`sec`.`contentupdated`,`sec`.`id`, (
        SELECT GROUP_CONCAT(`sub1`.`pagetraffic3`) AS `pagetraffic2`
        FROM (
                SELECT bob.timestamp, Max( bob.count ) AS pagetraffic3
                FROM (
                        SELECT timestamp, 0 AS count
                        FROM stats_hours
                        UNION
                        SELECT s.timestamp, s.count
                        FROM stats_pagetraffic s
                        WHERE s.page_id =`sec`.`id`
                        AND s.timestamp >= '20090821000000'
                        AND s.timestamp <= '20090821230000'
                ) AS bob
                GROUP BY bob.timestamp) as sub1
                ORDER BY sub1.timestamp ASC
        ) AS `pagetraffic`, (
        SELECT SUM(`stats_pagetraffic`.`count`)
        FROM `stats_pagetraffic`
        WHERE `stats_pagetraffic`.`page_id`=`sec`.`id` AND `stats_pagetraffic`.`timestamp`>='20090821000000' AND `stats_pagetraffic`.`timestamp`<='20090821230000'
) AS `count`
FROM `sections` AS `sec`
ORDER BY `count` DESC  LIMIT 5

Line 11 is the problem - WHERE s.page_id =`sec`.`id` (`sec`.`id` is an unknown column)
I presume this is because you can't pass values between multiple sub queries? The only way I can think to get around this is to write it all as one query using joins, but then that would mean rewriting the original query to work in a different way.

Gah!