1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL output hours in a day

Discussion in 'HTML, Graphics & Programming' started by toastyman, 18 Aug 2009.

  1. toastyman

    Gangster

    Joined: 30 Dec 2005

    Posts: 421

    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?
     
    Last edited: 18 Aug 2009
  2. tonyyeb

    Mobster

    Joined: 31 May 2006

    Posts: 4,236

    Location: 127.0.0.1

    Not sure it can be done in the SQL statement as checking for a record that isn't there whilst returning records that are there is tricky.

    Is this going into a webpage/application? What programming language you using?
     
  3. toastyman

    Gangster

    Joined: 30 Dec 2005

    Posts: 421

    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.
     
  4. tonyyeb

    Mobster

    Joined: 31 May 2006

    Posts: 4,236

    Location: 127.0.0.1

    I'd be tempted to do it in PHP and then put that calculation into a page that can be included in the other pages.

    As for doing a query within a query... that is a possibility but like you I can see it in my head but putting it into a query is failing me at the moment!!
     
  5. SimonCHere

    Wise Guy

    Joined: 24 Jun 2008

    Posts: 1,168

    Clunkyness below!

    Code:
    SELECT bob.timestamp,Max(bob.cnt) as pagetraffic
      FROM  (select convert(datetime,'2009-08-18 12:00:00',120) as timestamp, 0 as cnt
            union 
            select convert(datetime,'2009-08-18 13:00:00',120) as timestamp, 0 as cnt
            union 
            select convert(datetime,'2009-08-18 14:00:00',120) as timestamp, 0 as cnt
            union
            select convert(datetime,'2009-08-18 15:00:00',120) as timestamp, 0 as cnt
            union 
            select s.timestamp ,s.cnt from stats_pagetraffic s where s.page_id = 1) as bob
    group by bob.timestamp
    
    Sorry MSSQL doesn't have the GROUP_CONCAT function.

    Code:
    output
    timestamp	pagetraffic
    18/08/2009 12:00:00	0
    18/08/2009 13:00:00	1
    18/08/2009 14:00:00	0
    18/08/2009 15:00:00	0
    18/08/2009 16:00:00	1
    18/08/2009 17:00:00	5
    18/08/2009 18:00:00	2
    
    I should have noted that all the union stuff was because I didn't want to create a table for the "missing" hours. you could just have had
    Code:
    SELECT bob.timestamp,Max(bob.cnt) as pagetraffic
      FROM  (select timestamp, 0 as cnt from [SomeTableWith24RowsInItForTheHours]
                 union
                select s.timestamp ,s.cnt from stats_pagetraffic s where s.page_id = 1) as bob
    group by bob.timestamp
    
     
    Last edited: 19 Aug 2009
  6. toastyman

    Gangster

    Joined: 30 Dec 2005

    Posts: 421

    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..
     
    Last edited: 19 Aug 2009
  7. SimonCHere

    Wise Guy

    Joined: 24 Jun 2008

    Posts: 1,168

    Glad it worked for you. I can't help with the GROUP_CONCAT bit as I don't have access to mySQL.

    You don't have to use 'bob' as the table alias though...;)
     
  8. toastyman

    Gangster

    Joined: 30 Dec 2005

    Posts: 421

    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 :(
     
  9. SimonCHere

    Wise Guy

    Joined: 24 Jun 2008

    Posts: 1,168

    in SQL you can do something like:
    Code:
    declare @combined varchar(2000)
    select @combined = isnull(@combined + ', ','') + 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
    
    But I'm not sure that will help you.

    How about

    Code:
    SELECT GROUP_CONCAT(`sub1`.`pagetraffic`) AS `pagetraffic` FROM 
    (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) as sub1
    ORDER BY sub1.timestamp ASC
    
     
  10. toastyman

    Gangster

    Joined: 30 Dec 2005

    Posts: 421

    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!