getting records for yesterday day starting till ending PostgreSQL

Currently my application is running over 2 dbs one is snapshotDB one is evercamDB. In snapshotDB we are saving records now it want to retrieve them , currently am using this query

select camera_id,count(*) as snapshot_count 
from snapshots 
where snapshot_id in (select snapshot_id 
                      from snapshots 
                      where created_at = 'now'::date - 1) 
group by camera_id

what i want is to get records from snapshots such as am considering today is 2016-01-21 and i want to get all records from yesterday. '2016-01-20 00:00:00' to '2016-01-20 23:59:59'. i really dont know how to bind time with 'now'::date - 1 in such format as i have written.

And i want to place this date and time in (select snapshot_id from snapshots where created_at = 'now'::date - 1) so that i can get records as i want.

Any suggestion or help will be appreciated

You need to cast created_at as a date - that will remove the time.

now() also contains a time, so you need to use current_date

And finally your IN (..) is not needed, you can apply that condition directly in the where clause:

select camera_id, count(*) as snapshot_count 
from snapshots 
where created_at::date = current_date - 1
group by camera_id;

Note that the above will not use an index on created_at. If the performance is not acceptable and the condition would benefit from using an index, you can use something like this:

select camera_id, count(*) as snapshot_count 
from snapshots 
where created_at >= date_trunc('day', current_timestamp) - interval '1' day
  and create_at < date_trunc('day', current_timestamp)
group by camera_id;

StackExchange Database Administrators Q#126982, answer score: 6