2

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

0
5

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
1
  • 2
    If there are gaps in the results (uppermost query) that you'd like to fill in, you can add a UNION statement selecting from generate_series, eg. union all select hr from generate_series(0,23,1) hr – bma Feb 2 '16 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.