There are two Postgres tables, one has the work roster with the planned times, the other has times the work was actually carried out. All times are stored as timestamps, the tables are linked with a job number in a 1:1 relationship. There are three KPIs around the work timing:

• a job starts within /-15 minutes of the planned start time
• a job finishes within /-15 minutes of the planned end time
• both start and finish time are within /- 15 minutes of the planned times

The desired outcome is a list of job numbers and a pass/fail string for each KPI. KPI report is currently run once a month, but we are looking to have this connected to a live dashboard. Volume is currently low (6500 rows/month), but has been rising at about 5-10% per month.

table_a

table_b

Result

In the absence of an abs(interval), currently I have two candidates for comparing the two times:

CASE
    WHEN ABS(TO_CHAR((table_a.rostered_start - table_b.actual_start) , 'MI')::integer) <= 15 
     THEN 'PASS'::TEXT
    ELSE 'FAIL'::TEXT
END AS start_time_check

or

CASE
    WHEN table_b.actual_start <= (table_a.rostered_start   '00:15:00'::INTERVAL)
         AND table_b.actual_start >= (table_a.rostered_start - '00:15:00'::INTERVAL)
      THEN 'PASS'::TEXT
    ELSE 'FAIL'::TEXT
END AS start_time_check

At the scale I'm working at it seems like it doesn't matter which one I choose, but:

• Is setting this up as a function overkill, but the "right" way to do this?
• Is there a better / faster / more efficient way?

CodePudding user response：

SELECT *, kpi_start AND kpi_end AS kpi_startend
FROM  (
   SELECT job_no
        , b.actual_start - a.rostered_start BETWEEN interval '- 15 min' AND interval '15 min' AS kpi_start
        , b.actual_end   - a.rostered_end   BETWEEN interval '- 15 min' AND interval '15 min' AS kpi_end
   FROM   tbl_a a
   JOIN   tbl_b b USING (job_no)
   ) sub;

Using more natural boolean values instead of PASS / FAIL. Easy to translate.

There is no built-in abs() function for the interval data type, just abs(numeric_type). But the BETWEEN ... AND ... construct is at least as performant and simple enough.