时间戳

首页 > 解决方案 > 查询以查找 3 个时间戳（ts），其中第一个最接近给定 ts，2 个最接近找到的第一个

postgresql - 查询以查找 3 个时间戳（ts），其中第一个最接近给定 ts，2 个最接近找到的第一个

问题描述

假设表有超过 5 个时间戳。我想首先获得与给定时间戳最接近的时间戳，而不是 2 个其他时间戳，这些时间戳是第一个之前和之后最近的时间戳。例如：我有 [3,1,4,2,5]。与第一个给定时间戳最接近的时间戳是 4，那么它将给出 3,4,5

标签： postgresql

解决方案

第一步是找到最接近给定值的时间戳

假设你有一个这样的简单表：

CREATE TABLE test (dt TIMESTAMP)

您可以通过搜索基准中的时间戳与给定值之间的最小间隔来找到最接近的时间戳，例如“2014-12-31T23:59:59Z”。为此，您可以使用类似的东西：

SELECT dt AS closest_timestamp FROM test ORDER BY (
   CASE WHEN((dt - '2014-12-31T23:59:59Z'::TIMESTAMP) < INTERVAL '0')
   THEN (-(dt - '2014-12-31T23:59:59Z'::TIMESTAMP)) ELSE
   (dt - '2014-12-31T23:59:59Z') END) ASC LIMIT 1;

'CASE' 部分用于具有绝对值的区间。然后您可以使用以下内容查找该值之前和之后的最近值：

WITH f AS (SELECT dt FROM test ORDER BY (CASE WHEN (
   (dt - '2014-12-31T23:59:59Z'::TIMESTAMP) < INTERVAL '0')
   THEN (-(dt - '2014-12-31T23:59:59Z'::TIMESTAMP)) ELSE 
   (dt - '2014-12-31T23:59:59Z'::TIMESTAMP) END) ASC LIMIT 1)
SELECT 
   f.dt AS closest_first,
   b.dt AS just_before_closest_first,
   a.dt AS just_after_closest_first
FROM
   f,
   (SELECT dt FROM test WHERE dt < (SELECT dt FROM f) ORDER BY dt DESC LIMIT 1) AS b,
   (SELECT dt FROM test WHERE dt > (SELECT dt FROM f) ORDER BY dt ASC LIMIT 1) AS a;

此解决方案存在一些问题，特别是如果在您的第​​一个最接近的值之前或之后您的基础中没有值，但在全球范围内它可以解决问题......

希望能帮助到你。

推荐阅读