postgresql - Creating sequence of dates and inserting each date into query -

i need find data within first day of current month last day of current month.

 select count(*) q_aggr_data         a.filial_='fil1'        , a.operator_ 'unit%'         , date_trunc('day',a.s_end_)='"+ date_to_search+ "'         group a.s_name_,date_trunc('day',a.s_end_) 

date_to_searh here 01.09.2014,02.09.2014, 03.09.2014,...,30.09.2014

i've tried loop through i=0...30 , make 30 queries, takes long , extremely naive. days there no entry should return 0. i've seen how generate date sequences, can't head around on how inject days 1 one query

by creating not series, set of 1 day ranges, timestamp data can joined range using >= <

note in particular approach avoids functions on data (such truncating date) , because of permits use indexes assist query performance.

if data looked this:

create table my_data     ("data_dt" timestamp) ;  insert my_data     ("data_dt") values     ('2014-09-01 08:24:00'),     ('2014-09-01 22:48:00'),     ('2014-09-02 13:12:00'),     ('2014-09-03 03:36:00'),     ('2014-09-03 18:00:00'), 

then can joined, using outer join unmatched ranges still reported generated set of ranges (dt_start & dt_end pairs)

select         r.dt_start       , count(d.data_dt) (       select             dt_start           , dt_start + interval '1 day' dt_end                   generate_series('2014-09-01 00:00'::timestamp,                            '2014-09-30 00:00', '1 day') dt_start      ) r left outer join my_data d on d.data_dt >= r.dt_start                          , d.data_dt <  r.dt_end group         r.dt_start order         r.dt_start ; 

and result such produced:

|                         dt_start | count | |----------------------------------|-------| | september, 01 2014 00:00:00+0000 |     2 | | september, 02 2014 00:00:00+0000 |     1 | | september, 03 2014 00:00:00+0000 |     2 | | september, 04 2014 00:00:00+0000 |     2 |  ... | september, 29 2014 00:00:00+0000 |     0 | | september, 30 2014 00:00:00+0000 |     0 | 

see sqlfiddle demo