Edit Question - KnowledgeHub

Title Body

I have unevenly distributed data (wrt date ) for a few years (2003-2008). I want to query data for a given set of start and end date, grouping the data by any of the supported intervals (day, week, month, quarter, year) in PostgreSQL 8.3 . The problem is that some of the queries give results continuous over the required period, as this one: select to_char(date_trunc('month',date), 'YYYY-MM-DD'), count(distinct post_id) from some_table where category_id = 1 and entity_id = 77 and entity2_id = 115 and date <= '2008-12-06' and date >= '2007-12-01' group by date_trunc('month',date) order by date_trunc('month',date); to_char | count ------------+------- 2007-12-01 | 64 2008-01-01 | 31 2008-02-01 | 14 2008-03-01 | 21 2008-04-01 | 28 2008-05-01 | 44 2008-06-01 | 100 2008-07-01 | 72 2008-08-01 | 91 2008-09-01 | 92 2008-10-01 | 79 2008-11-01 | 65 (12 rows) But some of them miss some intervals because there is no data present, as this one: select to_char(date_trunc('month',date), 'YYYY-MM-DD'), count(distinct post_id) from some_table where category_id=1 and entity_id = 75 and entity2_id = 115 and date <= '2008-12-06' and date >= '2007-12-01' group by date_trunc('month',date) order by date_trunc('month',date); to_char | count ------------+------- 2007-12-01 | 2 2008-01-01 | 2 2008-03-01 | 1 2008-04-01 | 2 2008-06-01 | 1 2008-08-01 | 3 2008-10-01 | 2 (7 rows) where the required resultset is: to_char | count ------------+------- 2007-12-01 | 2 2008-01-01 | 2 2008-02-01 | 0 2008-03-01 | 1 2008-04-01 | 2 2008-05-01 | 0 2008-06-01 | 1 2008-07-01 | 0 2008-08-01 | 3 2008-09-01 | 0 2008-10-01 |

Tags (comma-separated) Cancel