From: | dud <sm90901(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to get results with zero count from this query? |
Date: | 2012-08-02 03:14:23 |
Message-ID: | 1343877263693-5718595.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Karl Denninger | 2012-08-02 03:22:41 | Re: Async replication: how to get an alert on failure |
Previous Message | leoxu | 2012-08-02 03:03:00 | 答复: 答复: [GENERAL] how to calculate or know seq_scan scan how many blocks every time |