Given a date/time range, how to filter results by time range?-CodePudding

I'm using PostgreSQL for this. I need to count the calls to the sales department during the week and for those that came in between business hours. Here's my query so far:

SELECT 
 SUM(CASE WHEN call_logs.to_phone_number IN ('15125551234') THEN 1 ELSE 0 END) as sales_all,
 SUM(
    CASE WHEN 
    call_logs.to_phone_number IN ('15125551234') AND call_logs.start_time >= '08/08/2022 7:30 AM' AND call_logs.start_time <= '08/08/2022 5:30 PM'
  OR
    call_logs.to_phone_number IN ('15125551234') AND call_logs.start_time >= '08/09/2022 7:30 AM' AND call_logs.start_time <= '08/09/2022 5:30 PM'
  OR
    call_logs.to_phone_number IN ('15125551234') AND call_logs.start_time >= '08/10/2022 7:30 AM' AND call_logs.start_time <= '08/10/2022 5:30 PM'
  OR
    call_logs.to_phone_number IN ('15125551234') AND call_logs.start_time >= '08/11/2022 7:30 AM' AND call_logs.start_time <= '08/11/2022 5:30 PM'  
  OR
    call_logs.to_phone_number IN ('15125551234') AND call_logs.start_time >= '08/12/2022 7:30 AM' AND call_logs.start_time <= '08/12/2022 5:30 PM'
    THEN 1 ELSE 0 END) as sales_between_business_hours,
FROM call_logs
WHERE call_logs.start_time >= '08-08-2022 12:00 AM' AND call_logs.start_time <= '08-14-2022 11:59 PM';

This works, but the the second case statement to get sales_between_business_hours seems a bit excessive. Anybody see a more elegant way to do this?

CodePudding user response：

Equivalent, less verbose, unambiguous, and faster, too:

SELECT count(*) AS sales_all
     , count(*) FILTER (WHERE start_time BETWEEN '2022-08-08 07:30' AND '2022-08-12 17:30'
                        AND   start_time::time BETWEEN '07:30' AND '17:30'
                       ) AS sales_between_business_hours
FROM   call_logs
WHERE  to_phone_number = '15125551234'
AND    start_time >= '2022-08-08'  -- always use unambiguous ISO format!
AND    start_time <= '2022-08-14 11:59';

Or, shorter yet (but not faster):

...
     , count(*) FILTER (WHERE start_time BETWEEN '2022-08-08' AND '2022-08-13'
                        AND   start_time::time BETWEEN '07:30' AND '17:30'
                       ) AS sales_between_business_hours
...

Or, while there are only a hand full of ranges, making use of the new multirange types and operators in Postgres 14 or later:

...
     , count(*) FILTER (WHERE start_time <@
                              tsmultirange '{[2022-08-08 07:30, 2022-08-08 17:30]
                                           , [2022-08-09 07:30, 2022-08-09 17:30]
                                           , [2022-08-10 07:30, 2022-08-10 17:30]
                                           , [2022-08-11 07:30, 2022-08-11 17:30]
                                           , [2022-08-12 07:30, 2022-08-12 17:30]}'
                       ) AS sales_between_business_hours
...

Yes, this is a valid timestamp literal: '2022-08-08' - same as '2022-08-08 00:00:00'. And it's best to always use unambiguous ISO 8601 format. See:

About aggregate FILTER:

Aggregate columns with additional (distinct) filters

BETWEEN always includes lower and upper bound, just like your original formulation does. If your timestamp values are not all truncated to minutes, you might reconsider, though, as either formulation includes the start of the full minute for the upper bound, but excludes the rest of it.

Perform this hours of operation query in PostgreSQL