时间戳

首页 > 解决方案 > 在 Oracle 中以非日期格式比较时间

oracle - 在 Oracle 中以非日期格式比较时间

问题描述

我用秒表测量时间，从几分钟到几小时不等（[hh:m]m:ss.ss）。我以为 Oracle 会有一些方法来比较时间，但我无法在他们的文档中找到它

我可以尝试使用某种正则表达式来计算秒数，但提供的格式并不统一。这是一个例子：

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

   27:54.92

   56:31.55

   2:49.38

假设我想将此时间与诸如此类的计分表进行比较

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

每个时间的分数是根据他们达到的阈值授予的，因此需要将我们测量的时间与评分表中的时间进行比较（如果时间在 20 分钟到 30 分钟之间，则得分为 70 分)

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

   27:54.92  |  70

   56:31.55  |  25

   2:49.38  |  100

标签： oracle

解决方案

您的时间应该被视为间隔，以便可以将它们作为正常数据进行操作：

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在这里

推荐阅读