1

I have this query to get records from a database:

SELECT camera_id, count(*) AS snapshot_count
FROM   snapshots
WHERE  created_at >= timestamp 'yesterday'
AND    created_at <  timestamp 'today'
GROUP  BY camera_id;

I want to add some filters on created_at to get the count for matching rows only. E.g., the user will provide camera_id , date, time, days, for example:

From date: 2015/01/01 
to date: 2015/12/30
Schedule: Monday-Friday
Timings: 9 AM to 5 pm UTC

How to apply all those to created_at to get valid records?

Update:

I also want a count per minute. If 1 minute contains more than 1 image it shall still be counted as one. You could say, only count the first snapshot per minute.

Shortly if it is 1 per 60 minutes then it will be 1 count for 60 minutes between 9 AM to 10AM and so as on to 5PM.

  • I took the liberty to rewrite the first part of your update. Did I understand correctly? I am at a loss what the second part might mean, though. – Erwin Brandstetter Feb 12 '16 at 8:39
  • per minutes simply means the gaps between time as if we are from 9 to 5 then it will be EVERY image. but per minutes means just one image from that "minutes" duration – Junaid Farooq Feb 12 '16 at 9:04
  • simply minutes should be variable as well so any amount of minute can be added such as 60 120 180 then it will consider count as one for per 60 minutes in the whole 9 to 5 pm time for each day so we can get the value for whole date span – Junaid Farooq Feb 12 '16 at 9:18
  • Please update the question to make it clear (click "edit" above). Add an example if you have trouble expressing it in plain English. – Erwin Brandstetter Feb 12 '16 at 21:33
  • It's not clear what your intention is. Are you asking for a way to detect, simply, that a given period has any snapshots? To say that a different way: do you want results that only read 1 or 0 for the snapshot_count column (per @ErwinBrandstetter 's answer)? – Chris Harrington Feb 13 '16 at 3:33
0

For lack of information, assuming created_at is data type timestamp, saving UTC timestamps:

SELECT camera_id, count(*) AS snapshot_count
FROM   snapshots
WHERE  created_at >= timestamp '2015-01-01'   -- From date: 2015/01/01 
AND    created_at <  timestamp '2015-12-31'   -- to date: 2015/12/30 
                                              -- chop off '2015-12-31'?
AND    created_at::time BETWEEN time '09:00' AND time '17:00'
                                              -- 9 AM to 5 AM UTC
                                              -- assuming you meant 5 PM
AND    EXTRACT('ISODOW' FROM created_at) < 6  -- Monday-Friday
GROUP  BY camera_id;
  • The check for date seems trivial - but be careful what to include / exclude exactly with upper lower bound.
  • For the time component cast to time: created_at::time.
  • For the day of week use EXTRACT() with the pattern ISODOW (simpler for your kind of pattern than DOW).

    In case you are not aware of the difference between the data types timestamp and timestamptz and the role of the timezone settings and DST, now is the time to read up:

  • Ignoring timezones altogether in Rails and PostgreSQL

Count minutes with multiple rows as one

For your added Update:

SELECT camera_id
     , count(DISTINCT date_trunc('minute', created_at) AS snapshot_minute_count
FROM ...

Effectively, minutes are counted in which at least one (filtered) snapshot was taken. If 7 snapshots were taken in the same minute, they still count as one.

Performance

The filters on time and day of week are not sargable. If your table is big and performance important, create functional indexes to change that - using IMMUTABLE expressions. Depending on your actual data type, you need to create an IMMUTABLE wrapper function that works independently of the timezone setting, because neither the cast to time nor EXTRACT() (internally the function date_part()) are defined IMMUTABLE for various reasons.

CREATE FUNCTION f_to_time(timestamp)
  RETURNS time LANGUAGE sql IMMUTABLE COST 5 AS
'SELECT $1::time';

CREATE OR REPLACE FUNCTION f_to_isodow(timestamp)
  RETURNS int LANGUAGE sql IMMUTABLE COST 20 AS
$$SELECT EXTRACT('ISODOW' FROM $1 AT TIME ZONE 'UTC')::int$$;

Then:

CREATE INDEX snapshots_created_at_time_idx ON snapshots (f_time(created_at));
CREATE INDEX snapshots_created_at_isodow_idx ON snapshots (f_to_isodow(created_at));

To utilize these indexes, the query hast to use the same expressions:

SELECT camera_id
     , count(*) AS snapshot_count
     , count(DISTINCT date_trunc('minute', created_at) AS snapshot_minute_count
FROM   snapshots
WHERE  created_at >= timestamp '2015-01-01'
AND    created_at <  timestamp '2015-12-31'
AND    f_time(created_at) BETWEEN time '09:00' AND time '17:00'
AND    f_to_isodow(created_at) < 6
GROUP  BY camera_id;
  • sorry for misunderstanding i edited my question please have a look now? – Junaid Farooq Feb 12 '16 at 7:17
  • @ijunaidfarooq: Consider the update. – Erwin Brandstetter Feb 12 '16 at 8:31
  • 1 per "minute" i want minute to be variable as well, IF user put 1 per "120"(minutes), it should work as well. You can resmove first Snapshot_count and assume we only need count with "Per Minutes clause." – Junaid Farooq Feb 12 '16 at 8:57

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.