Get Statistical data from table in PostgreSQL using single query

Question

I am using PostgreSQL database, I have the requirement where I have to provide statistical data for the configurable timespan. I have pasted the sample data of my table.

**id   timeStamp        caseId**  
224 2017-05-30 12:47:27 4654
226 2017-05-30 1:59:46  5165
225 2017-05-30 1:53:31  4658
223 2017-05-30 2:44:45  4656
221 2017-05-30 4:05:53  4645
220 2017-05-30 6:05:12  4640

if the time span is 1 hour then, I want the count records for each hour like [1,2,1,0,1,0,1] and so on...

I can do that using 12 different query with start and end date. but I want the output in one query.

Is it possible in one query.?

@VaoTsun timestamp is data type of that column, which stores data and time. — Mitul Maheshwari
– Mitul Maheshwari, Commented May 31, 2017 at 9:06

Laurenz Albe · Accepted Answer · 2017-05-31 09:16:41Z

1

You can use an outer join with a table function that produces the hours you want to query.

This assumes that your table is called incidents:

SELECT hours.h, count(i)
FROM incidents i
   RIGHT JOIN generate_series(
                 TIMESTAMP '2017-05-30 00:00:00',
                 TIMESTAMP '2017-05-30 23:00:00',
                 INTERVAL '1 hour'
              ) hours(h)
      ON i.timestamp >= hours.h
         AND i.timestamp < hours.h + INTERVAL '1 hour'
GROUP BY hours.h;

If you want the result as an array, omit hours.h from the SELECT list and surround the query with

SELECT array_agg(count)
   FROM (...) q;

answered May 31, 2017 at 9:16

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mitul Maheshwari Over a year ago

Thank you for the answer, looks great. I wanted to know in case of minute how it will work.

Laurenz Albe Over a year ago

The same, just change the interval.

Collectives™ on Stack Overflow

Get Statistical data from table in PostgreSQL using single query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related