4

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

Junaid Farooq
  • 281
  • 1
  • 6
  • 16

2 Answers2

6

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;
0
SELECT camera_id,
       count(*) AS snapshot_count 
FROM snapshots 
WHERE created_at >= current_timestamp - interval '1 day' 
  AND create_at < current_date - 1   
GROUP BY camera_id;
Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
user194091
  • 1