On 08/01/12 8:14 PM, dud wrote:
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: 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: 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 -e.g. 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 the rows with 0 counts in addition to the original results? Thanks in advance.
You can probably do a left join with generate_series for the hour range you want... the count() of the mssing values will probably be NULL, so you may need to coalesce that to 0.
-- john r pierce N 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
