PostgreSQL – How to Get Records for Yesterday by Hour

postgresqlpostgresql-9.3

I am currently getting all data for yesterday from my database but I want to get data for each hour. This is my query:

select
    camera_id,
    count(*) as snapshot_count
from snapshots
where created_at >= TIMESTAMP 'yesterday' 
  and created_at <  TIMESTAMP 'today'
group by camera_id

It's giving me all records for yesterday, but how could it be possible to get records from yesterday but for each hour?

PS: I want to run this in one query, not 24 queries

Best Answer

Use date_part to get the hour part of your timestamp, then add it to your GROUP BY clause.

 select
    camera_id,
    count(*) as snapshot_count,
    date_part('hour', created_at) as hr
 from snapshots
 where created_at >= TIMESTAMP 'yesterday' AND created_at < TIMESTAMP 'today'
 group by camera_id, hr

I generated an example table:

CREATE TABLE ex.snapshots
(
  camera_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
)

and populated it with some test data:

INSERT INTO ex.snapshots (camera_id,created_at) (
    SELECT
        (random() * 6)::integer,
        now() - (((random() * 2000)::integer)::text || ' minutes')::interval
        FROM generate_series(1,20000)
    );

After adding an ordering clause, here's the first few rows of output:

  camera_id | snapshot_count | hr 
 -----------+----------------+----
          0 |             39 |  0
          0 |             46 |  1
          0 |             59 |  2
          0 |             46 |  3
          0 |             49 |  4
          0 |             48 |  5
          0 |             56 |  6
          0 |             43 |  7
Related Question