Snowflake UDF in JavaScript does not calculate as expected

I am trying to calculate the number of minutes a logged job has been running. Each job has a start time and an end time.

In this particular case, the working hours are between 01:00 and 10:00, and only business days (weekends excluded)

In order to calculate this, I tried and made a JavaScript based UDF like this:

CREATE OR REPLACE FUNCTION JobRuns(f datetime, t datetime)
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
AS
$$
    // Based on the Calculation of Business Hours in JavaScript
    // https://www.c-sharpcorner.com/UploadFile/36985e/calculating-business-hours-in-javascript/
    
    function workingMinutesBetweenDates(startDate, endDate) {    
        // Store minutes worked    
        var minutesWorked = 0;    
    
        // Validate input    
        if (endDate < startDate) {    
            return 0;    
        }    
        
        // Loop from your Start to End dates (by hour)    
        var current = startDate;    
        
        // Define work range    
        var workHoursStart = 1;    
        var workHoursEnd = 10;    
        var includeWeekends = false;    
        
        // Loop while currentDate is less than end Date (by minutes)    
        while (current <= endDate) {    
            // Is the current time within a work day (and if it occurs on a weekend or not)     
            if (current.getHours() >= workHoursStart && current.getHours() <= workHoursEnd && (includeWeekends ? current.getDay() !== 0 && current.getDay() !== 6 : true)) {    
                minutesWorked++;    
            }    
        
            // Increment current time    
            current.setTime(current.getTime() + 1000 * 60);    
        }    
        
        // Return the number of minutes
        return minutesWorked;    
    }   
    return workingMinutesBetweenDates(F,T);
$$
;

But the result I am getting are in some cases rather off from what I had expected.

enter image description here

The JS logic is grabbed from here; https://www.c-sharpcorner.com/UploadFile/36985e/calculating-business-hours-in-javascript/ and when I look at the code, I cannot see any flaws, which might cause these discrepancies.

I am using these test data

CREATE OR REPLACE TABLE "SLA_Test" (
    "DocumentID" VARCHAR(16777216),
    "From" TIMESTAMP_NTZ(9),
    "To" TIMESTAMP_NTZ(9),
    "ExpectedTime" INT
  );
  
  INSERT INTO "SLA_Test"
  VALUES
    ('ACD7EFC1-8D17-46E3-84DB-C08067466866','2021-03-03 07:12:34.567','2021-03-03 08:12:34.567',60),
    ('C41FB599-D1EC-4461-BBAF-1AFF67D2F3C2','2021-03-03 09:55:00.000','2021-03-04 01:05:00.000',10),
    ('B741C663-732B-4FD3-839D-E70330C58990','2021-03-03 09:55:00.000','2021-03-04 00:05:00.000',5),
    ('C5893C51-F5CE-40E4-85F7-775515BC3E3D','2021-03-03 19:55:00.000','2021-03-04 01:05:00.000',5),
    ('BAF4ED57-8184-4CDF-8875-DFDA6EAC2033','2021-03-03 09:55:00.000','2021-03-05 01:05:00.000',550),
    ('F325059E-E78F-4DCE-B675-CC1C59669B3C','2021-03-05 09:55:00.000','2021-03-08 01:05:00.000',10),
    ('F325059E-E78F-4DCE-B675-CC1C59669B3C','2021-03-05 09:55:00.000','2021-03-07 01:05:00.000',5);

SELECT "DocumentID","From","To",
    DATEDIFF(second, "From", "To") AS "TotalElapsedTimeSecond",
    DATEDIFF(second, "From", "To")/60 AS "TotalElapsedTimeMinut",
    "ExpectedTime",
    JobRuns("From","To") AS "ElapsedTimeMinut"
FROM "SLA_Test";

Any ideas why the UDF does not return the expected time?

Answer

this can all be done in SQL,

with SLA_Test(DocumentID, FromTime, ToTime, ExpectedTime) AS (
  SELECT column1, column2::timestamp_ntz, column3::timestamp_ntz, column4 
  FROM
  VALUES
    ('ACD7EFC1-8D17-46E3-84DB-C08067466866','2021-03-03 07:12:34.567','2021-03-03 08:12:34.567',60),
    ('C41FB599-D1EC-4461-BBAF-1AFF67D2F3C2','2021-03-03 09:55:00.000','2021-03-04 01:05:00.000',10),
    ('B741C663-732B-4FD3-839D-E70330C58990','2021-03-03 09:55:00.000','2021-03-04 00:05:00.000',5),
    ('C5893C51-F5CE-40E4-85F7-775515BC3E3D','2021-03-03 19:55:00.000','2021-03-04 01:05:00.000',5),
    ('BAF4ED57-8184-4CDF-8875-DFDA6EAC2033','2021-03-03 09:55:00.000','2021-03-05 01:05:00.000',550),
    ('F325059E-E78F-4DCE-B675-CC1C59669B3C','2021-03-05 09:55:00.000','2021-03-08 01:05:00.000',10),
    ('F325059E-E78F-4DCE-B675-CC1C59669B3C','2021-03-05 09:55:00.000','2021-03-07 01:05:00.000',5)
), days as (
    SELECT row_number() over(order by seq8())-1 as num
    FROM table(GENERATOR(rowcount=>30))
), enriched as (
    SELECT *,
        datediff('day', s.fromtime, s.totime) as tot_days
  from SLA_Test AS s
), day_sliced AS (
  select s.*
      ,d.*
      ,date_trunc('day',fromtime) f_s
      ,dateadd('day', d.num, f_s) as clip_day
      ,dateadd('hour', 1, clip_day) as clip_start
      ,dateadd('hour', 10, clip_day) as clip_end
      ,dayofweekiso(clip_day) as dowi
      ,dowi >=1 AND dowi <= 5 as work_day
      ,least(greatest(s.fromtime, clip_start),clip_end) as slice_start
      ,greatest(least(s.totime, clip_end), clip_start) as slice_end
      ,DATEDIFF('second', slice_start, slice_end) as slice_sec
      ,DATEDIFF('minute', slice_start, slice_end) as slice_min
  from enriched AS s
  join days AS d on d.num <= s.tot_days
  qualify work_day = true
)
SELECT 
    DocumentID
    ,FromTime
    ,ToTime
    ,ExpectedTime
    ,round(sum(slice_sec)/60,0) as elasped_time_minutes
FROM day_sliced
GROUP BY 1,2,3,4
ORDER BY 1,2;

which gives the results as noted in expected:

DOCUMENTID                              FROMTIME                TOTIME     EXPECTEDTIME ELASPED_TIME_MINUTES
ACD7EFC1-8D17-46E3-84DB-C08067466866    2021-03-03 07:12:34.567 2021-03-03 08:12:34.567 60  60
B741C663-732B-4FD3-839D-E70330C58990    2021-03-03 09:55:00.000 2021-03-04 00:05:00.000 5   5
BAF4ED57-8184-4CDF-8875-DFDA6EAC2033    2021-03-03 09:55:00.000 2021-03-05 01:05:00.000 550 550
C41FB599-D1EC-4461-BBAF-1AFF67D2F3C2    2021-03-03 09:55:00.000 2021-03-04 01:05:00.000 10  10
C5893C51-F5CE-40E4-85F7-775515BC3E3D    2021-03-03 19:55:00.000 2021-03-04 01:05:00.000 5   5
F325059E-E78F-4DCE-B675-CC1C59669B3C    2021-03-05 09:55:00.000 2021-03-07 01:05:00.000 5   5
F325059E-E78F-4DCE-B675-CC1C59669B3C    2021-03-05 09:55:00.000 2021-03-08 01:05:00.000 10  10