sql - 从表中选择时间戳在彼此一小时内的项目

我正在使用 postgres (postgis) 并且我想执行一个查询，该查询返回所有具有一个小时内时间戳的行。

SQL 语句是什么样的？

该表如下所示:

id { 整数}

名称 { 字符变化 }

时间戳 { 带时区的时间戳 }

id   name       timestamp
---+----------+--------------------------------
1    one        "2010-09-24 21:10:39.515+00"
2    two        "2010-09-16 09:21:09.362+00"
3    three      "2010-07-08 00:00:46.549+00"

编辑 #1

这是一个更好的例子。 (在 Tometzky 的帮助下)所有结果都在 1 小时之内。也就是说，对于每一行，给我 1 小时内的任何其他行:

select * from myTable order by t

 id |               t
----+-------------------------------
  9 | 2011-07-15 18:20:20.05+02
 10 | 2011-07-15 19:05:00.05+02
 11 | 2011-07-15 19:40:20.05+02
 13 | 2011-07-15 20:31:01.05+02
 14 | 2011-07-15 20:35:11.05+02
(5 rows)

result of needed query:

 id |  matchid |  origTime                  | matchTime
----+----------+----------------------------+------------------------------
  9 |   10     | 2011-07-15 18:20:20.05+02  | 2011-07-15 19:05:00.05+02
 10 |    9     | 2011-07-15 19:05:00.05+02  | 2011-07-15 18:20:20.05+02
 10 |   11     | 2011-07-15 19:05:00.05+02  | 2011-07-15 19:40:20.05+02
 11 |   10     | 2011-07-15 19:40:20.05+02  | 2011-07-15 19:05:00.05+02
 11 |   13     | 2011-07-15 19:40:20.05+02  | 2011-07-15 20:31:01.05+02
 11 |   14     | 2011-07-15 19:40:20.05+02  | 2011-07-15 20:35:11.05+02
 13 |   11     | 2011-07-15 20:31:01.05+02  | 2011-07-15 19:40:20.05+02
 13 |   14     | 2011-07-15 20:31:01.05+02  | 2011-07-15 20:35:11.05+02
 14 |   11     | 2011-07-15 20:35:11.05+02  | 2011-07-15 19:40:20.05+02
 14 |   13     | 2011-07-15 20:35:11.05+02  | 2011-07-15 20:31:01.05+02
(10 rows)

最佳答案

select
    t0.id, t1.id as matchid,
    t0.ts as origTime, t1.ts as matchTime
from t t0
inner join t t1 on
    t1.ts between
        t0.ts - interval '1 hour'
        and
        t0.ts + interval '1 hour'
    and t0.id != t1.id
;

关于sql - 从表中选择时间戳在彼此一小时内的项目，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6709876/