从日志表中计算访问时长的SQL查询

Question

我有一个表格，记录了每次加载网页时的用户ID、课程、会话ID和请求日期。 我想计算给定课程ID下每个用户的持续时间。 由于时间段重叠，这样做存在问题。

提供的数据应该导致课程1中每个用户的持续时间为10分钟。 但我似乎做不对。

CREATE TABLE PageLogSample (
    id INT NOT NULL PRIMARY KEY IDENTITY
,   userid INT
,   courseid INT
,   sessionid INT
,   requestdate DATETIME
);

TRUNCATE TABLE PageLogSample;

INSERT INTO PageLogSample (userid, courseid, sessionid, requestdate)
-- [0, 10] = 10 minutes
          SELECT 1, 1, 1, '00:00:00'
UNION ALL SELECT 1, 1, 1, '00:10:00'
-- [0, 12] - [3, 5] = 10 minutes
-- or ... [0, 3] + [5, 12] = 10 minutes
UNION ALL SELECT 2, 1, 2, '00:00:00'
UNION ALL SELECT 2, 2, 2, '00:03:00'
UNION ALL SELECT 2, 2, 2, '00:05:00'
UNION ALL SELECT 2, 1, 2, '00:12:00'
-- [0, 12] - [3, 5] = 10 minutes
-- or ... [0, 3] + [5, 12] = 10 minutes
UNION ALL SELECT 3, 1, 3, '00:00:00'
UNION ALL SELECT 3, 2, 3, '00:03:00'
UNION ALL SELECT 3, 2, 3, '00:05:00'
UNION ALL SELECT 3, 1, 3, '00:12:00'
UNION ALL SELECT 3, 2, 3, '00:15:00'
-- [1, 13] - [3, 5] = 10 minutes
-- or ... [1, 3] + [5, 13] = 10 minutes
UNION ALL SELECT 4, 2, 4, '00:00:00'
UNION ALL SELECT 4, 1, 4, '00:01:00'
UNION ALL SELECT 4, 2, 4, '00:03:00'
UNION ALL SELECT 4, 2, 4, '00:05:00'
UNION ALL SELECT 4, 1, 4, '00:13:00'
UNION ALL SELECT 4, 2, 4, '00:15:00'
-- [0, 5] + [10, 15] = 10 minutes
UNION ALL SELECT 5, 1, 5, '00:00:00'
UNION ALL SELECT 5, 1, 5, '00:05:00'
UNION ALL SELECT 5, 1, 6, '00:10:00'
UNION ALL SELECT 5, 1, 6, '00:15:00'
-- [0, 10] = 10 minutes (ignoring everything inbetween)
UNION ALL SELECT 6, 1, 7, '00:00:00'
UNION ALL SELECT 6, 1, 7, '00:03:00'
UNION ALL SELECT 6, 1, 7, '00:05:00'
UNION ALL SELECT 6, 1, 7, '00:07:00'
UNION ALL SELECT 6, 1, 7, '00:10:00'
-- [0, 11] - [5, 6] = 10 minutes
-- or ... [0, 3] + [7, 11] = 6 minutes (good)
-- or ... [0, 5] + [7, 11] = 9 minutes (better)
UNION ALL SELECT 7, 1, 8, '00:00:00'
UNION ALL SELECT 7, 1, 8, '00:03:00'
UNION ALL SELECT 7, 2, 8, '00:05:00'
UNION ALL SELECT 7, 2, 8, '00:06:00'
UNION ALL SELECT 7, 1, 8, '00:07:00'
UNION ALL SELECT 7, 1, 8, '00:11:00'
-- [0, 1] + [2, 4] + [5, 7] + [8, 13] = 10
UNION ALL SELECT 8, 1, 9, '00:00:00'
UNION ALL SELECT 8, 2, 9, '00:01:00'
UNION ALL SELECT 8, 1, 9, '00:02:00'
UNION ALL SELECT 8, 1, 9, '00:03:00'
UNION ALL SELECT 8, 2, 9, '00:04:00'
UNION ALL SELECT 8, 1, 9, '00:05:00'
UNION ALL SELECT 8, 1, 9, '00:06:00'
UNION ALL SELECT 8, 2, 9, '00:07:00'
UNION ALL SELECT 8, 1, 9, '00:08:00'
UNION ALL SELECT 8, 1, 9, '00:13:00'
;

首先尝试朴素的方法。这种方法会在会话的重叠部分产生错误。

DECLARE @courseid INT;
SET @courseid = 1;

SELECT subquery.userid
, COUNT(DISTINCT subquery.sessionid) AS sessioncount
, SUM(subquery.duration) AS duration
, CASE SUM(subquery.duration) 
    WHEN 10 THEN 'ok' 
    ELSE 'ERROR' 
END
FROM (
    SELECT userid
    , sessionid
    , DATEDIFF(MINUTE, MIN(requestdate), MAX(requestdate)) AS duration
    FROM PageLogSample
    WHERE courseid = @courseid
    GROUP BY userid
    , sessionid
) subquery
GROUP BY subquery.userid
ORDER BY subquery.userid;

-- userid  sessioncount  duration   
-- 1       1             10       ok
-- 2       1             12       ERROR
-- 3       1             12       ERROR
-- 4       1             12       ERROR
-- 5       2             10       ok

第二次尝试。避免重叠。这只能部分地起作用。

DECLARE @courseid INT;
SET @courseid = 1;

WITH cte (userid, courseid, sessionid, start, finish, duration)
AS (
    SELECT userid
    , courseid
    , sessionid
    , MIN(requestdate)
    , MAX(requestdate)
    , DATEDIFF(MINUTE, MIN(requestdate), MAX(requestdate))
    FROM PageLogSample
    GROUP BY userid
    , courseid
    , sessionid
)
SELECT naive.userid
, naive.sessioncount
, naive.duration AS naiveduration
, correction.duration AS correctionduration
, naive.duration - ISNULL(correction.duration, 0) AS duration
, CASE naive.duration - ISNULL(correction.duration, 0)
    WHEN 10 THEN 'ok' 
    ELSE 'ERROR' 
END
FROM (
    SELECT cte.userid
    , COUNT(DISTINCT cte.sessionid) AS sessioncount
    , SUM(cte.duration) AS duration
    FROM cte
    WHERE cte.courseid = @courseid
    GROUP BY cte.userid
) naive
LEFT JOIN (
    SELECT errors.userid
    , SUM(errors.duration) AS duration
    FROM cte errors
    WHERE errors.courseid <> @courseid
    AND EXISTS (
        SELECT *
        FROM cte
        WHERE cte.start <= errors.start
        AND cte.finish >= errors.finish
        AND cte.courseid = @courseid
    )
    GROUP BY errors.userid
) correction
ON naive.userid = correction.userid
;

-- userid  sessioncount  naiveduration  correctionduration  duration
-- 1       1             10             NULL                10        ok
-- 2       1             12             2                   10        ok
-- 3       1             12             NULL                12        ERROR
-- 4       1             12             NULL                12        ERROR
-- 5       2             10             NULL                10        ok

更新： Ed Harper的评论 让我重新思考了我的方法。

所以这是第三次尝试。首先，我搜索哪些行代表进入课程，哪些行代表离开课程。然后，我将所有结束时间相加并减去所有开始时间的总和。我认为这样更正确，虽然不完美。

DECLARE @courseid INT;
SET @courseid = 1;

WITH numberedcte (rn, id, userid, courseid, sessionid, requestdate)
AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY sessionid, userid ORDER BY id)
    , id
    , userid
    , courseid
    , sessionid
    , requestdate
    FROM PageLogSample
)
, typedcte (rowtype, id, userid, courseid, sessionid, requestdate, nextrequestdate)
AS (
    SELECT CASE
        WHEN previousrequest.courseid = nextrequest.courseid
            THEN 'between'
        WHEN previousrequest.courseid IS NULL
            OR nextrequest.courseid = numberedcte.courseid
            THEN 'begin'
        WHEN nextrequest.courseid IS NULL
            OR previousrequest.courseid = numberedcte.courseid
            THEN 'end'
        ELSE 'error?'
    END AS rowtype
    , numberedcte.id
    , numberedcte.userid
    , numberedcte.courseid
    , numberedcte.sessionid
    , numberedcte.requestdate
    , nextrequest.requestdate
    FROM numberedcte
    LEFT JOIN numberedcte previousrequest
        ON previousrequest.userid = numberedcte.userid
        AND previousrequest.sessionid = numberedcte.sessionid
        AND previousrequest.rn = numberedcte.rn - 1
    LEFT JOIN numberedcte nextrequest
        ON nextrequest.userid = numberedcte.userid
        AND nextrequest.sessionid = numberedcte.sessionid
        AND nextrequest.rn = numberedcte.rn + 1
    WHERE numberedcte.courseid = @courseid
    AND (
        nextrequest.courseid = @courseid
        OR previousrequest.courseid = @courseid
    )
)
, beginsum (userid, value)
AS (
    SELECT userid, SUM(DATEPART(MINUTE, requestdate))
    FROM typedcte
    WHERE rowtype = 'begin'
    GROUP BY userid
)
, endsum (userid, value)
AS (
    SELECT userid, SUM(DATEPART(MINUTE, ISNULL(nextrequestdate, requestdate)))
    FROM typedcte
    WHERE rowtype = 'end'
    GROUP BY userid
)
SELECT beginsum.userid
, endsum.value - beginsum.value AS duration
FROM beginsum
INNER JOIN endsum
    ON beginsum.userid = endsum.userid
;

唯一的问题是，从我的原始样本数据中，我只能获得用户1和5的输出。新增的用户6也给出了正确的输出。现在新增的用户7给出了令人满意的输出。用户8几乎完美，但第一行到第二行缺少一分钟。

-- userid  duration
-- 1       10
-- 5       10
-- 6       10
-- 7       9
-- 8       9

我觉得我离完全正确只差一步。唯一缺失的是没有发生在组内的页面请求持续时间。有人能帮我找到获取孤立页面浏览次数的方法吗？

更新： 这是第四次尝试。我给每个请求分配一个值并将它们相加。虽然它没有给我期望的输出，但看起来足够好。

DECLARE @courseid INT;
SET @courseid = 1;

WITH numberedcte (rn, userid, courseid, sessionid, requestdate)
AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY sessionid, userid ORDER BY id)
    , userid
    , courseid
    , sessionid
    , requestdate
    FROM PageLogSample
)
, valuecte (value, userid, courseid, sessionid)
AS (
    SELECT CASE
        --alone
        WHEN ( previousrequest.courseid IS NULL
            OR previousrequest.courseid <> numberedcte.courseid
            )
            AND nextrequest.courseid <> numberedcte.courseid
            THEN DATEDIFF(MINUTE, numberedcte.requestdate, nextrequest.requestdate)
        --between
        WHEN previousrequest.courseid = nextrequest.courseid
            THEN 0
        --begin
        WHEN previousrequest.courseid IS NULL
            OR nextrequest.courseid = numberedcte.courseid
            THEN -1 * DATEPART(MINUTE, numberedcte.requestdate)
        --ignored (end with no next request)
        WHEN nextrequest.courseid IS NULL
            AND previousrequest.courseid <> numberedcte.courseid
            THEN 0
        --end
        WHEN nextrequest.courseid IS NULL
            OR previousrequest.courseid = numberedcte.courseid
            THEN DATEPART(MINUTE, ISNULL(nextrequest.requestdate, numberedcte.requestdate))
        --impossible?
        ELSE 0
    END
    , numberedcte.userid
    , numberedcte.courseid
    , numberedcte.sessionid
    FROM numberedcte
    LEFT JOIN numberedcte previousrequest
        ON previousrequest.userid = numberedcte.userid
        AND previousrequest.sessionid = numberedcte.sessionid
        AND previousrequest.rn = numberedcte.rn - 1
    LEFT JOIN numberedcte nextrequest
        ON nextrequest.userid = numberedcte.userid
        AND nextrequest.sessionid = numberedcte.sessionid
        AND nextrequest.rn = numberedcte.rn + 1
    WHERE numberedcte.courseid = @courseid
)
SELECT userid
, courseid
, COUNT(DISTINCT sessionid) AS sessioncount
, SUM(value) AS duration
FROM valuecte
GROUP BY userid
, courseid
ORDER BY userid
;

正如你所看到的，结果并不完全符合我的预期。

-- userid  courseid  sessioncount  duration
-- 1       1         1             10
-- 2       1         1              3
-- 3       1         1              6
-- 4       1         1              4
-- 5       1         2             10
-- 6       1         1             10
-- 7       1         1              9
-- 8       1         1             10

我的本地数据库的性能非常糟糕。如果有人有更高效的写法，请分享。

更新： 性能提升了。我添加了一个索引，现在运行得非常好。

- Kristof Neirynck

---

很好的问题 - 提供脚本和示例，使解决变得更容易。 - Andrew

数据的难点在于，requestdate没有一致的含义。有时它是课程的开始时间，有时是结束时间。 - Ed Harper

很好的问题，赞一个！ - Mark Allison

为了帮助我理解这个逻辑，你能否解释一下如果我们查看的是CourseID 2，UserID 3的持续时间会是多少呢？谢谢！ - WesleyJohnson

我感觉用户3在第2门课程上花了9分钟。(5-3)+(12-5)+(？-15)=9。问号表示我不知道他在最后一页停留了多长时间，因为没有后续页面请求。也许我应该解释得更清楚一些。这是服务器上网页请求的日志。每一行都是一个页面请求。这些课程是站点的不同部分。我的任务是找出用户每门课程所花费的时间。有一个以前的实现，我需要替换它，因为它不够快。理想情况下，我的结果应该与旧的实现相同。 - Kristof Neirynck

Answer 1

这是我能做到的最接近的。它在用户ID 4处失败。

正如我在评论中所说，requestdate有时是课程的开始，有时是结束，我无法看出在给定行上它扮演哪个角色的简单通用规则。

DECLARE @courseid INT;
SET @courseid = 1;

WITH orderCTE
AS
(
        SELECT *

               ,ROW_NUMBER() OVER (PARTITION BY sessionid
                                   ORDER BY id
                                  ) AS rn
        FROM PageLogSample
        --order by rn
)
,startendCTE
AS
(
        SELECT  CASE WHEN start1.rn = 1
                     THEN start1.courseid
                     ELSE end1.courseid
                 END courseid
                ,start1.sessionid
                ,start1.userid
                ,DATEDIFF(mi,start1.requestdate,end1.requestdate) duration
        FROM orderCTE AS start1
        JOIN orderCTE AS end1
        ON end1.rn = start1.rn + 1
        AND end1.sessionid = start1.sessionid
)
SELECT courseid
       ,COUNT(1) sessionCount
       ,userid
       ,SUM(duration) totalDuration
FROM startendCTE
WHERE courseid = @courseid
GROUP BY courseid
         ,userid;

- Ed Harper

---

我喜欢先搜索起始行和结束行的想法。你激发了我采取新的方法。 - Kristof Neirynck

Answer 2

这有点凌乱，但似乎对于CourseID 1有效。我没有尝试过其他课程，所以您可能需要测试一下！:D

基本前提是我正在获取目标CourseID的第一个和最后一个会话之间的时间持续时间，然后我正在减去任何不属于指定CourseID但会话请求时间落在目标CourseID的最小和最大请求时间之间的会话的持续时间。希望这讲得通。

查询肯定可以进行清理，可能使用CTE或其他内容。顺便说一句，这是个有趣的问题！:)

DECLARE @courseid INT;
SET @courseid = 1;

SELECT 
    TargetCourse.UserID, 
    COUNT(Distinct(TargetCourse.SessionID)) as SessionCount,
    SUM(TargetCourse.Duration - Coalesce(OtherCourses.Duration,0)) as Duration
FROM
(
    SELECT 
        TargetCourse.UserID, TargetCourse.SessionID, 
        MIN(TargetCourse.RequestDate) FirstRequest, MAX(TargetCourse.RequestDate) LastRequest, 
        DATEDIFF(MINUTE, MIN(TargetCourse.RequestDate), MAX(TargetCourse.RequestDate)) AS duration
    FROM 
        PageLogSample TargetCourse
    WHERE
        TargetCourse.CourseID = @courseid
    GROUP BY
        TargetCourse.UserID, TargetCourse.SessionID     
) as TargetCourse
LEFT OUTER JOIN
(
    SELECT 
        OtherCourses.UserID, OtherCourses.SessionID, 
        MIN(OtherCourses.RequestDate) AS FirstRequest, MAX(OtherCourses.RequestDate) AS LastRequest, 
        DATEDIFF(MINUTE, MIN(OtherCourses.RequestDate), MAX(OtherCourses.RequestDate)) AS duration
    FROM 
        PageLogSample OtherCourses
    WHERE
        OtherCourses.CourseID <> @courseid AND
        OtherCourses.RequestDate between
            (Select MIN(RequestDate) From PageLogSample T Where T.UserID = OtherCourses.UserID and T.CourseID = @courseid) AND
            (Select MAX(RequestDate) From PageLogSample T Where T.UserID = OtherCourses.UserID and T.CourseID = @courseid)
    GROUP BY
        OtherCourses.UserID, OtherCourses.SessionID 
) as OtherCourses ON
OtherCourses.UserID = TargetCourse.UserID AND
OtherCourses.FirstRequest BETWEEN TargetCourse.FirstRequest and TargetCourse.LastRequest
Group By TargetCourse.UserID

- WesleyJohnson

---

非常好，但还不完美。当你搜索要减去的时间时，需要记住可能会有多个具有相同courseid的“间隙”。我添加了一个额外的用户到示例数据中来展示这一点。 - Kristof Neirynck

啊哈，谢谢你指出来。我会添加额外的用户并再试一次。 :) - WesleyJohnson

Answer 3

一些更多的样本数据和一个希望合理的假设，即每个用户在每门课程中花费了多少时间。

INSERT INTO PageLogSample (userid, courseid, sessionid, requestdate)
-- [0, 10] = 10 minutes
          SELECT 1, 1, 1, '00:00:00'
UNION ALL SELECT 1, 1, 1, '00:10:00'
-- [0, 3] = 3 minutes
-- there is no way to know how long the user was on that last page
UNION ALL SELECT 2, 1, 2, '00:00:00'
UNION ALL SELECT 2, 2, 2, '00:03:00'
UNION ALL SELECT 2, 2, 2, '00:05:00'
UNION ALL SELECT 2, 1, 2, '00:12:00'
-- [0, 3] + [12, 15] = 6 minutes
-- the [5, 12] part was spent on a page of course 2
UNION ALL SELECT 3, 1, 3, '00:00:00'
UNION ALL SELECT 3, 2, 3, '00:03:00'
UNION ALL SELECT 3, 2, 3, '00:05:00'
UNION ALL SELECT 3, 1, 3, '00:12:00'
UNION ALL SELECT 3, 2, 3, '00:15:00'
-- [1, 3] + [13, 15] = 4 minutes
UNION ALL SELECT 4, 2, 4, '00:00:00'
UNION ALL SELECT 4, 1, 4, '00:01:00'
UNION ALL SELECT 4, 2, 4, '00:03:00'
UNION ALL SELECT 4, 2, 4, '00:05:00'
UNION ALL SELECT 4, 1, 4, '00:13:00'
UNION ALL SELECT 4, 2, 4, '00:15:00'
-- [0, 5] + [10, 15] = 10 minutes
UNION ALL SELECT 5, 1, 5, '00:00:00'
UNION ALL SELECT 5, 1, 5, '00:05:00'
UNION ALL SELECT 5, 1, 6, '00:10:00'
UNION ALL SELECT 5, 1, 6, '00:15:00'
-- [0, 10] = 10 minutes (ignoring everything inbetween)
UNION ALL SELECT 6, 1, 7, '00:00:00'
UNION ALL SELECT 6, 1, 7, '00:03:00'
UNION ALL SELECT 6, 1, 7, '00:05:00'
UNION ALL SELECT 6, 1, 7, '00:07:00'
UNION ALL SELECT 6, 1, 7, '00:10:00'
-- [0, 5] + [7, 11] = 9 minutes
UNION ALL SELECT 7, 1, 8, '00:00:00'
UNION ALL SELECT 7, 1, 8, '00:03:00'
UNION ALL SELECT 7, 2, 8, '00:05:00'
UNION ALL SELECT 7, 2, 8, '00:06:00'
UNION ALL SELECT 7, 1, 8, '00:07:00'
UNION ALL SELECT 7, 1, 8, '00:11:00'
-- [0, 1] + [2, 4] + [5, 7] + [8, 13] = 10
UNION ALL SELECT 8, 1, 9, '00:00:00'
UNION ALL SELECT 8, 2, 9, '00:01:00'
UNION ALL SELECT 8, 1, 9, '00:02:00'
UNION ALL SELECT 8, 1, 9, '00:03:00'
UNION ALL SELECT 8, 2, 9, '00:04:00'
UNION ALL SELECT 8, 1, 9, '00:05:00'
UNION ALL SELECT 8, 1, 9, '00:06:00'
UNION ALL SELECT 8, 2, 9, '00:07:00'
UNION ALL SELECT 8, 1, 9, '00:08:00'
UNION ALL SELECT 8, 1, 9, '00:13:00'
-- there is nothing we can say about either of there requests
-- 0 minutes
UNION ALL SELECT 9, 1, 10, '00:10:00'
UNION ALL SELECT 9, 1, 11, '00:20:00'
;

现在我们是这样获取数据的：

WITH numberedcte (rn, userid, courseid, sessionid, requestdate)
AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY sessionid, userid ORDER BY id)
    , userid
    , courseid
    , sessionid
    , requestdate
    FROM PageLogSample
)
, valuecte (value, userid, courseid, sessionid)
AS (
    SELECT CASE
        --alone in session
        WHEN previousrequest.courseid IS NULL
            AND nextrequest.courseid  IS NULL
            THEN 0
        --alone
        WHEN ( previousrequest.courseid IS NULL
            OR previousrequest.courseid <> numberedcte.courseid
            )
            AND nextrequest.courseid <> numberedcte.courseid
            THEN DATEDIFF(MINUTE, numberedcte.requestdate, nextrequest.requestdate)
        --between
        WHEN previousrequest.courseid = nextrequest.courseid
            THEN 0
        --begin
        WHEN previousrequest.courseid IS NULL
            OR nextrequest.courseid = numberedcte.courseid
            THEN -1 * DATEPART(MINUTE, numberedcte.requestdate)
        --ignored (end with no next request)
        WHEN nextrequest.courseid IS NULL
            AND previousrequest.courseid <> numberedcte.courseid
            THEN 0
        --end
        WHEN nextrequest.courseid IS NULL
            OR previousrequest.courseid = numberedcte.courseid
            THEN DATEPART(MINUTE, ISNULL(nextrequest.requestdate, numberedcte.requestdate))
        --impossible?
        ELSE 0
    END
    , numberedcte.userid
    , numberedcte.courseid
    , numberedcte.sessionid
    FROM numberedcte
    LEFT JOIN numberedcte previousrequest
        ON previousrequest.userid = numberedcte.userid
        AND previousrequest.sessionid = numberedcte.sessionid
        AND previousrequest.rn = numberedcte.rn - 1
    LEFT JOIN numberedcte nextrequest
        ON nextrequest.userid = numberedcte.userid
        AND nextrequest.sessionid = numberedcte.sessionid
        AND nextrequest.rn = numberedcte.rn + 1
    WHERE numberedcte.courseid = @courseid
)
SELECT userid
, courseid
, COUNT(DISTINCT sessionid) AS sessioncount
, SUM(value) AS duration
FROM valuecte
GROUP BY userid
, courseid
ORDER BY userid
;

这是我得到的结果。我对它感到非常满意。请注意，用户9的会话计数仍然正确。

userid  courseid  sessioncount  duration
1       1         1             10
2       1         1              3
3       1         1              6
4       1         1              4
5       1         2             10
6       1         1             10
7       1         1              9
8       1         1             10
9       1         2              0

- Kristof Neirynck

Answer 4

抱歉，我认为您有一个数据问题。查看提供的样本数据，用户2在课程ID 1上学习了12分钟，在课程ID 2上学习了2分钟。

您确定已经提供了正确的数据吗？

- armitage

---

数据是正确的，但很难从中获取相关的含义。用户2开始在课程1中，然后去了课程2两分钟，然后回到课程1。我想知道他在课程1中花费的时间（10分钟）。因此，12分钟减去他在另一个课程中花费的2分钟。 - Kristof Neirynck

看来你是对的。我的原始数据解释有误。 - Kristof Neirynck

Answer 5

数据是正确的，但很难从中获取相关的含义。

我不得不回应这是一个自相矛盾的说法。你不知道意义的数据不是数据。

至于你最初的问题：

你需要的是一个提供良好支持间隔类型的数据库管理系统。没有SQL系统能够达到这个水平。除了一些教程系统外，我的DBMS（在这种情况下不再推广，因此没有链接）是我所知道的唯一一个真正为此类问题提供所需支持的系统。

如果你有兴趣，可以在谷歌上搜索“间隔类型”、“紧凑正常形式”、“时间数据”，最终你会找到它。

- Erwin Smout