Compare time in a non-date format in Oracle

Question

I have time measured with a stopwatch, ranging from a few minutes to a few hours([hh:m]m:ss.ss). I assumed Oracle would have some way to compare times but I wasn't able to find it in their docs

I could try to use some kind of regex expression to calculate the number of seconds, but the format provided is not uniform. Here's an example:

TIME
----------
01:42:25.76

   27:54.92

   56:31.55

   2:49.38

Let's say I want to compare this times with a scoring table such as this one

TIME         | SCORE
----------------
   10:00.00  |  100

   20:00.00  |  85

   30:00.00  |  70

   40:00.00  |  55

   50:00.00  |  40

01:00:00.00  |  25

The score for each of the times is granted according to the threshold they reached, so it is necessary to compare our measured times to the ones in the scoring table (if the time is between 20 min and 30 min, the score would be 70 points)

TIME         | SCORE
----------------
01:42:25.76  |  0

   27:54.92  |  70

   56:31.55  |  25

   2:49.38  |  100

Answer 1

I used both answers listed to develop my solution. I created a function to format the data with LPAD to make it uniform, then stored them as intervals with to_dsinterval function

create or replace function time_format(str varchar2) return interval day to second 
is 
begin 
    case when instr(str,':')<>0 then 
        return to_dsinterval('0 '||lpad(str,11,'00:')); 
    else 
        return numtodsinterval(str, 'SECOND'); 
    end case; 
end time_format; 

Answer 2

Your times should be treated as intervals so that they can be manipulated as normal data:

WITH cteBase_scoring AS (SELECT INTERVAL '10:00.00' MINUTE TO SECOND AS TIME, 100 AS SCORE FROM DUAL UNION ALL
                         SELECT INTERVAL '20:00.00' MINUTE TO SECOND, 85 FROM DUAL UNION ALL
                         SELECT INTERVAL '30:00.00' MINUTE TO SECOND, 70 FROM DUAL UNION ALL
                         SELECT INTERVAL '40:00.00' MINUTE TO SECOND, 55 FROM DUAL UNION ALL
                         SELECT INTERVAL '50:00.00' MINUTE TO SECOND, 40 FROM DUAL UNION ALL
                         SELECT INTERVAL '01:00:00.00' HOUR TO SECOND, 25 FROM DUAL),
     cteScoring AS (SELECT LAG(bs.TIME, 1, INTERVAL '00:00' MINUTE TO SECOND)
                             OVER (ORDER BY bs.TIME) AS FROM_TIME,
                           bs.TIME AS TO_TIME,
                           bs.SCORE
                      FROM cteBase_scoring bs),
     cteTimes AS (SELECT INTERVAL '01:42:25.76' HOUR TO SECOND AS TIME FROM DUAL UNION ALL
                  SELECT INTERVAL '27:54.92' MINUTE TO SECOND FROM DUAL UNION ALL
                  SELECT INTERVAL '56:31.55' MINUTE TO SECOND FROM DUAL UNION ALL
                  SELECT INTERVAL '02:49.38' MINUTE TO SECOND FROM DUAL)
SELECT t.TIME, NVL(s.SCORE, 0) AS SCORE
  FROM cteTimes t
  LEFT OUTER JOIN cteScoring s 
    ON t.TIME > s.FROM_TIME AND
       t.TIME <= s.TO_TIME;

dbfiddle here

Answer 3

You can use LPAD(time, 11, '00:') to make your input uniform to the format HH24:MI:SS.xx and then use simple string compare, as then the lexicographical order coincides with the time order:

ScoreTable:

TIME         | LPAD...     | SCORE
-------------+-------------+------
   10:00.00  | 00:10:00.00 | 100
   20:00.00  | 00:20:00.00 |  85
   30:00.00  | 00:30:00.00 |  70
   40:00.00  | 00:40:00.00 |  55
   50:00.00  | 00:50:00.00 |  40
01:00:00.00  | 01:00:00.00 |  25

Results:

TIME         | LPAD...    
-------------+------------
01:42:25.76  | 01:42:25.76
   27:54.92  | 00:27:54.92
   56:31.55  | 00:56:31.55
   02:49.38  | 00:02:49.38

You can then optain the score with the following query:

SELECT time
     , nvl((select max(score)
              from score 
             where score.lpad >= results.lpad), 0) as score
  from results