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 &gt;= date_trunc('day', current_timestamp) - interval '1' day
  and create_at &lt; date_trunc('day', current_timestamp)
group by camera_id;