Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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)
- 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)
- 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 | 2
- 2008-11-01 | 0
- (12 rows)
- select distinct date_trunc('month', (current_date - offs)) as date
- from generate_series(0,365,28) as offs;
- date
- ------------------------
- 2007-12-01 00:00:00+01
- 2008-01-01 00:00:00+01
- 2008-02-01 00:00:00+01
- 2008-03-01 00:00:00+01
- 2008-04-01 00:00:00+02
- 2008-05-01 00:00:00+02
- 2008-06-01 00:00:00+02
- 2008-07-01 00:00:00+02
- 2008-08-01 00:00:00+02
- 2008-09-01 00:00:00+02
- 2008-10-01 00:00:00+02
- 2008-11-01 00:00:00+01
- 2008-12-01 00:00:00+01
Add Comment
Please, Sign In to add comment