CODEWITHSUNDEEP
sqlpostgresql
Jens
Jens

Reputation: 291

How to divide time period to parts?

There is a column in the table that stores the time when the record was created. The query specifies the end and start dates, then the interval is divided into n parts. And within each gap I need to calculate the number of records from the database. Tell me what to add to the query. Also, I need to count the number of records even if they were not at that time.

F.e.

----------------------------
 id |      query date      |
----------------------------
1   | 2017-06-08 01:23:00  |
2   | 2017-06-08 01:24:19  |
3   | 2017-06-08 01:24:21  |
4   | 2017-06-08 01:24:36  |
5   | 2017-06-08 01:24:37  |
6   | 2017-06-08 01:24:41  |
----------------------------

I choose period from 2017-06-08 01:24:00 to 2017-06-08 01:26:00 and devide this period to 4 parts, then I wait for

------------------------------
 count |      query date      |
------------------------------
2      | 2017-06-08 01:24:00  |
3      | 2017-06-08 01:24:30  |
0      | 2017-06-08 01:25:00  |
0      | 2017-06-08 01:25:30  |
------------------------------

My start

select to_timestamp(extract(epoch from query_date)/extract(epoch FROM age('2017-06-08 01:25:00', '2017-06-08 01:24:00'))/60), count(*) from logs group by extract(epoch from query_date)/extract(epoch FROM age('2017-06-08 01:25:00', '2017-06-08 01:24:00'))/60;

Upvotes: 1

Views: 646

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

try generate_series, smth like :

t=# with a as (
        with ali as (
                select g from generate_series('2017-06-08 01:24:00','2017-06-08 01:26:00','30 seconds'::interval) g
        )
        select g as t1, lead(g) over (order by g) t2
        from ali
        limit 4
)
select count(id), a.t1, coalesce(avg(id),0)
from a
left outer join logs l on l.query_date >= t1 and l.query_date <t2
group by a.t1
order by t1;
 count |           t1           |      coalesce
-------+------------------------+--------------------
     2 | 2017-06-08 01:24:00+00 | 2.5000000000000000
     3 | 2017-06-08 01:24:30+00 | 5.0000000000000000
     0 | 2017-06-08 01:25:00+00 |                  0
     0 | 2017-06-08 01:25:30+00 |                  0
(4 rows)

updated to reconsile with OP notice - I used coalesce to "set default value to zero" for rows where avg() returns NULL

Upvotes: 1

Related Questions