Q:

Creating sequence of dates and inserting each date into query

I need to find certain data within first day of current month to the last day of current month.
 select count(*) from q_aggr_data as a 
       where a.filial_='fil1'
       and a.operator_ like 'unit%' 
       and date_trunc('day',a.s_end_)='"+ date_to_search+ "' 
       group by a.s_name_,date_trunc('day',a.s_end_)

date_to_searh here is 01.09.2014,02.09.2014, 03.09.2014,...,30.09.2014
I've tried to loop through i=0...30 and make 30 queries, but that takes too long and extremely naive. Also to the days where there is no entry it should return 0. I've seen how to generate date sequences, but can't get my head around on how to inject those days one by one into the query

A:

By creating not only a series, but a set of 1 day ranges, any timestamp data can be joined to the range using >= with < 
Note in particular that this approach avoids functions on the data (such as truncating to date) and because of this it permits the use indexes to assist query performance.
If some data looked like this:
CREATE TABLE my_data
    ("data_dt" timestamp)
;

INSERT INTO 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 that can be joined, using an outer join so unmatched ranges are still reported to a generated set of ranges (dt_start & dt_end pairs)
SELECT
        r.dt_start
      , count(d.data_dt)
FROM (
      SELECT
            dt_start
          , dt_start + INTERVAL '1 Day' dt_end 
      FROM
           generate_series('2014-09-01 00:00'::timestamp,
                           '2014-09-30 00:00', '1 Day') AS dt_start
     ) AS r
LEFT OUTER JOIN my_data d ON d.data_dt >= r.dt_start
                         AND d.data_dt <  r.dt_end
GROUP BY
        r.dt_start
ORDER BY
        r.dt_start
;

and a result such as this is 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 this SQLFiddle demo

