postgresql 查找任意时间戳的前后时间戳

Question

给定一个任意时间戳，例如 2014-06-01 12:04:55-04 我可以在某个表中找到之前和之后的时间戳。然后，我使用以下查询计算这两者之间经过的秒数:

SELECT EXTRACT (EPOCH FROM (
  (SELECT time AS t0 
    FROM sometable 
    WHERE time < '2014-06-01 12:04:55-04' 
    ORDER BY time DESC LIMIT 1) -
  (SELECT time AS t1
    FROM sometable
    WHERE time > '2014-06-01 12:04:55-04' 
    ORDER BY time ASC LIMIT 1)
)) as elapsedNegative;

`它有效，但我想知道是否有另一种更优雅或更精明的方法来实现相同的结果？我正在使用 9.3。这是一个玩具数据库。

CREATE TABLE sometable (
id serial,
time timestamp
);

INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 11:59:37-04');
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 12:02:22-04');
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 12:04:49-04');
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 12:07:35-04');
INSERT INTO sometable (id, time) VALUES (1, '2014-06-01 12:09:53-04');

感谢任何提示...

更新 感谢@Joe Love 和@Clément Prévost 提供有趣的替代方案。一路上学到了很多!

Answer 1

鉴于 sometable.time 列已被索引，您的原始查询不会更有效，您的执行计划应该仅显示 2 次索引扫描，这是非常有效的(如果您有 pg 9.2 及更高版本，则仅索引扫描)。

这里有一个更具可读性的写法

WITH previous_timestamp AS (
    SELECT time AS time 
    FROM sometable 
    WHERE time < '2014-06-01 12:04:55-04' 
    ORDER BY time DESC LIMIT 1
), 
next_timestamp AS (
    SELECT time AS time
    FROM sometable
    WHERE time > '2014-06-01 12:04:55-04' 
    ORDER BY time ASC LIMIT 1
)
SELECT EXTRACT (EPOCH FROM (
  (SELECT * FROM next_timestamp) 
  - (SELECT * FROM previous_timestamp)
))as elapsedNegative;

使用 CTE 允许您通过命名子查询来赋予它意义。显式命名是众所周知且公认的编码最佳实践(使用显式名称，不要缩写，也不要使用通用名称，如“数据”或“值”)。

请注意，CTE 是优化“栅栏”，有时会妨碍规划器优化

这里是 the SQLFiddle .

编辑:将提取物从 CTE 移动到最终查询，以便 PostgreSQL 可以使用仅索引扫描。