CODEWITHSUNDEEP
postgresql
Simlet
Simlet

Reputation: 43

How to query hourly aggregated data by date with postgresql?

There is one table:

ID DATE 
1  2017-09-16 20:12:48
2  2017-09-16 20:38:54
3  2017-09-16 23:58:01
4  2017-09-17 00:24:48
5  2017-09-17 00:26:42
..

The result I need is the last 7-days of data with hourly aggregated count of rows:

COUNT DATE
2     2017-09-16 21:00:00
0     2017-09-16 22:00:00
0     2017-09-16 23:00:00
1     2017-09-17 00:00:00
2     2017-09-17 01:00:00
..

I tried different stuff with EXTRACT, DISTINCT and also used the generate_series function (most stuff from similar stackoverflow questions)

This try was the best one currently:

SELECT 
  date_trunc('hour', demotime) as date,
  COUNT(demotime) as count
FROM demo
GROUP BY date

How to generate hourly series for 7 days and fill-in the count of rows?

Upvotes: 2

Views: 3543

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

SQL DEMO

SELECT dd, count("demotime")     
FROM generate_series
        ( current_date - interval '7 days'
        , current_date 
        , '1 hour'::interval) dd
LEFT JOIN Table1        
  ON dd = date_trunc('hour', demotime)        
GROUP BY dd;

To work from now and now - 7 days:

SELECT dd, count("demotime")     
FROM generate_series
        ( date_trunc('hour', NOW()) - interval '7 days'
        , date_trunc('hour', NOW())
        , '1 hour'::interval) dd
LEFT JOIN Table1        
  ON dd = date_trunc('hour', demotime)        
GROUP BY dd;

Upvotes: 4

Related Questions