#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    Getting the zero values along with the original queries


    Hello, I have a database table that contains information about the timestamp and location(latitude and longtitude) of the requests made by users. The column structure is as following:


    Code:
    requesttime(which is a timestamp without time zone type) | latitude | longtitude

    I have written the following code in order to retrieve the total requests made day by day and hour interval by hour interval starting from a given datetime to another given datetime:


    Code:
    SELECT date_trunc('hour', requesttime), COUNT(requesttime) 
    FROM mytable 
    WHERE requesttime between '2001-04-02 03:12:45' and '2006-02-05 23:14:00' 
    GROUP BY date_trunc('hour', requesttime) 
    ORDER BY date_trunc('hour', requesttime);
    (a sample result from this query is 2003-07-11 21:00:00 | 121, meaning that 121 requests were made during the 21:00 - 22:00 hourly interval on july 11 2003)


    however, I realized that this query skips printing out the time intervals that have 0 requests -as an example let's say that 2002-03-12 03:00:00 (the 03:00 - 04:00 am interval) has 0 counts of requests but it directly skips printing that and prints the 04:00-05:00 interval instead-


    How can I make this query to also print out all the rows in my table with 0 counts in addition to all the original results I have printed? Thanks in advance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    You need to generate a list of "available" times and dates. This can easily be done using generate_series(). Something like this:

    Code:
    select timestamp '2001-04-02 00:00:00' + interval '1' hour * i
    from generate_series(0,23) i
    This will generate one row for each hour on April 2nd.

    Once you have that list you outer join that against your table:

    Code:
    SELECT date_trunc('hour', t.hr), COUNT(m.requesttime) 
    FROM (
       select timestamp '2001-04-02 00:00:00' + interval '1' hour * i as hr
       from generate_series(0,23) i
    ) t
    LEFT JOIN mytable m 
           ON date_trun(m.requesttime) = date_trunc('hour', t.hr)
          AND requesttime between '2001-04-02 03:12:45' AND '2006-02-05 23:14:00' 
    GROUP BY date_trunc('hour', t.hr) 
    ORDER BY date_trunc('hour', t.hr);
    It's important to put the interval restriction into the JOIN clause, otherwise the outer join is turned into an inner join.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0
    Hello and thank you for your reply. I have one question though, I tried the code -also changed the 24 hour generator to

    Code:
    select timestamp without time zone '2011-02-02 00:00:00'::timestamp without time zone+generate_series(0,23)*'1 hour'::interval
    because it gave 24 rows of 00:00:00. I have also added 'hour' to the date_trunc with missing parameter at

    Code:
    ON date_trunc(m.calltime) = date_trunc('hour', t.hr)
    Now I'm getting the following error:

    Code:
    ERROR:  column t.hr does not exist
    LINE 19: ... ON date_trunc('hour',m.calltime) = date_trunc('hour', t.hr)

    The final version of the code I have modified is now as follows: - I also tried various combinations such as SELECT EXTRACT- in order to place instead of t.hr -

    Code:
    SELECT date_trunc('hour', t.hr), COUNT(m.calltime) 
    FROM (
       select timestamp without time zone '2011-02-02 00:00:00'::timestamp without time zone+generate_series(0,23)*'1 hour'::interval
    ) t
    LEFT JOIN visiobackup m 
           ON date_trunc('hour', m.calltime) = date_trunc('hour', t.hr)
          AND calltime between '2011-02-02 00:00:00' AND '2012-04-01 05:00:00' 
    GROUP BY date_trunc('hour', t.hr) 
    ORDER BY date_trunc('hour', t.hr);
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    You need to give the column from the generate series an alias:

    Code:
    select  .... generate_series(0,23)*'1 hour'::interval as hr
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0
    Thanks a lot, that did the trick. I have two more issues that I would like to clarify:

    Is there a way to make this joining process run once and be done with it -applying it to the database permanently, or maybe the problem is with the structure my original query choice since it still seems to ignore the rows with 0 count-? Because depending on date-time inputs from the Qt gui end, the two timestamp intervals will be varying -such as the 1 year interval in my example- and only a single day of 24 hour data execution took 14 seconds when my old query -the one in my first post, showing all hourly interval counts from February 2 2011 to April 1 2012- executed in 5 seconds.

    Secondly, executing this only returns the 24 hours of data of the first

    Code:
    select timestamp without time zone '2011-04-05 00:00:00'
    element. How can I make this return all the data within the given two timestamp parameters?

IMN logo majestic logo threadwatch logo seochat tools logo