Creating sequence of dates and inserting each date into query

Question

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

It will give count of whatever that has date within days in between. While I need date-count rows, where in the date there is every day of the month as a column — Nazerke
– Nazerke, Commented Sep 19, 2014 at 11:08

Paul Maxwell · Accepted Answer · 2014-09-19 12:38:30Z

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

Aret · Accepted Answer · 2014-09-19 11:31:48Z

0

One way to solve this problem is to group by truncated date.

select count(*)
from q_aggr_data as a 
where a.filial_='fil1'
and a.operator_ like 'unit%' 
group by date_trunc('day',a.s_end_), a.s_name_;

The other way is to use a window function, for getting the count over truncated date for example.

answered Sep 19, 2014 at 11:31

Aret

4753 silver badges10 bronze badges

Comments

ilitirit · Accepted Answer · 2014-09-19 12:09:52Z

0

Please check if this query satisfies your requirements:

select sum(matched) -- include s_name_, s_end_ if you want to verify the results
from
(select a.filial_
, a.operator_
, a.s_name_
, generate_series s_end_
, (case when a.filial_ = 'fil1' then 1 else 0 end) as matched
from q_aggr_data as a 
right join generate_series('2014-09-01', '2014-09-30', interval '1 day')
     on a.s_end_ = generate_series
     and a.filial_ = 'fil1'
     and a.operator_ like 'unit%') aa
group by s_name_, s_end_
order by s_end_, s_name_

http://sqlfiddle.com/#!15/e8edf/3

answered Sep 19, 2014 at 12:09

ilitirit

16.4k19 gold badges78 silver badges117 bronze badges

Collectives™ on Stack Overflow

Creating sequence of dates and inserting each date into query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related