数据库中2个条目之间的时间度量

Question

我正试图编写一个查询来测量两个事件之间的时间。假设数据库

中有以下条目

时间戳2012-04-29 22:07:22.342000 UTC2012-04-29 22:07:24.342000 UTC2012-04-29 22:07:22.342000 UTC2012-04-29 22:07:27.342000 UTC2012-04-29 22:07:22.342000 UTC

我认为这样的东西会得到你想要的，应该在任何平台上工作。

SELECT UserID,
CASE 
WHEN endpoint_200 IS NOT NULL THEN endpoint_called-endpoint_200
WHEN endpoint_400 IS NOT NULL THEN endpoint_called-endpoint_400
ELSE whatever_your_timeout_count_is_supposed_to_be
END as ResultField
FROM (
SELECT UserID,
MAX(CASE WHEN Event = 'endpoint_called' THEN timestamp 
ELSE NULL END) as endpoint_called,
MAX(CASE WHEN Event = 'endpoint_200' THEN timestamp 
ELSE NULL END) as endpoint_200,
MAX(CASE WHEN Event = 'endpoint_400' THEN timestamp 
ELSE NULL END) as endpoint_400
FROM YourTable
GROUP BY UserID
) sub