查找 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提供的有趣替代方案。过程中学到了很多！

- jjap

---

lag()和lead()窗口函数可以计算您所需的内容 http://www.postgresql.org/docs/current/static/functions-window.html -- 此外，时间戳差异可以通过其运算符轻松计算（无需多个extract()调用）http://www.postgresql.org/docs/current/static/functions-datetime.html - pozs

@pozs 谢谢，我知道这些函数。然而，由于我的任意时间戳不是表的一部分，所以我不确定如何获取“第一个以下”或“第一个以上”... - jjap

你的查询看起来非常有效，因为sometable.time列已经被索引了，你的执行计划应该会有2个索引扫描（如果你使用的是pg 9.2及以上版本，则只有索引扫描）。你有任何性能问题吗？ - Clément Prévost

@Clément Prévost 不，没有性能问题。由于我在SQL方面很新手，所以我认为我的代码可能不是很优化。很高兴发现它并不差！ - jjap

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可以通过命名子查询来赋予其含义。显式命名是一个众所周知的编程最佳实践（使用明确的名称，不要缩写，也不要使用过于通用的名称，如“data”或“value”）。

请注意，CTE是优化“障碍”，有时会妨碍计划器的优化。

这里是SQLFiddle。

编辑：将从CTE中提取的内容移动到最终查询中，以便PostgreSQL可以使用索引扫描。

- Clément Prévost

---

确实更易读了。基准测试结果为52.761毫秒，我认为这可能是您所提到的栅栏效应。关于我对@Joe Love的评论，请注意时间列没有索引... - jjap

哦，如果该列没有索引，那就是另一回事了。然后规划器必须选择顺序扫描，因为他不知道表中有什么。然后数据很快在数据页中找到，并且LIMIT子句非常快地结束查询。您应该根据物理表中的数据位置获得不一致的时间。您可以尝试其他日期值吗？ - Clément Prévost

这是正确的，但这些不是同一种操作。当您在PostgreSQL中创建主键时，数据库会自动在该列上创建索引。主键应该在整个行的“标识符”含义受到尊重时使用。如果不是这样，请使用唯一约束和索引。 - Clément Prévost

好的，如果您有唯一约束条件，时间列已经被索引（postgresql 也会自动为您完成）。我需要处理解释计划以确定如何克服优化隔离效应:D - Clément Prévost

这个解决方案的性能更好吗？ - Clément Prévost

Answer 2

如果时间戳列没有索引，这个解决方案可能会表现得更好。当9.4版本发布时，我们可以使用聚合过滤器来缩短一点。

这应该会更快一些，因为它只运行了一个完整的表扫描，而不是两个，但是如果你的时间戳列有索引并且数据集很大，则可能表现更差。

以下是没有时期转换的示例，以使其更易于阅读。

select 
min(
case when start_timestamp > current_timestamp
then
start_timestamp
else 'infinity'::timestamp
end 
),
max(
case when t1.start_timestamp < current_timestamp
then
start_timestamp
else '-infinity'::timestamp
end 
)
 from my_table as t1

这里是包含数学和时代提取的示例：

select
extract (EPOCH FROM (
min(
case when start_timestamp > current_timestamp
then
start_timestamp
else 'infinity'::timestamp
end 
)-
max(
case when start_timestamp < current_timestamp
then
start_timestamp
else '-infinity'::timestamp
end 
)))
 from snap.offering_event

如果你需要更多细节，请告诉我——我建议尝试我的代码并比较其性能和你的。

- Joe Love

---

没有索引的情况下，根据我生成的数据来看，查询速度肯定更快；但是如果使用B树索引，查询速度可能会更快——我忘记了B树索引可以用于优化order by和order by desc。 - Joe Love

@Joe Love。谢谢，使用CASE使代码更易读。仅供记录（只有30,000行），我的原始查询运行时间为0.772毫秒，而您的运行时间为37.916毫秒。 - jjap

我假设你的时间戳字段已经被索引了？ - Joe Love

我的查询可能会使用更多的CPU（通常查询是IO绑定的，不会使用大量的CPU）...因为我有2个case语句和一个max和min，所以也许就是这样..像-infinity时间戳这样的小东西可能会起到作用，但如果不进行更多的基准测试很难说。 - Joe Love

另外，你有在每个查询上多次运行计时吗？如此短的时间很可能会有很大的差异。 - Joe Love