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.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-get-results-with-zero-count-from-this-query-tp5718595.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
