Wählen Sie Elemente aus der Tabelle aus, deren Zeitstempel innerhalb einer Stunde liegen

Question

Ich verwende Postgres (Postgis) und möchte eine Abfrage durchführen, die alle Zeilen zurückgibt, die einen Zeitstempel innerhalb einer Stunde voneinander haben.

Wie würde die SQL-Anweisung aussehen?

Die Tabelle sieht wie folgt aus:

• id { ganze Zahl }
• Name { Zeichen variierend }

• Zeitstempel { Zeitstempel mit Zeitzone }

  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"

EDIT #1

Hier ist ein besseres Beispiel. (mit Hilfe von Tometzky) Alle Ergebnisse liegen innerhalb von 1 Stunde auseinander. Das heißt, für jede Zeile geben Sie mir alle anderen Zeilen, die innerhalb von 1 Stunde sind:

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)

Answer 1

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
;

Answer 2

Etwa so:

$ select * from test;
 id |               t               
----+-------------------------------
  9 | 2011-07-15 18:31:01.059487+02
 10 | 2011-07-15 18:31:01.55044+02
 11 | 2011-07-15 18:31:01.850583+02
 12 | 2011-07-15 18:31:02.064435+02
 13 | 2011-07-15 18:31:02.333449+02
 14 | 2011-07-15 18:31:02.727461+02
 15 | 2011-07-15 18:31:03.279447+02
 16 | 2011-07-15 18:31:03.642454+02
 17 | 2011-07-15 18:33:34.910252+02
 18 | 2011-07-15 18:33:35.995455+02
 19 | 2011-07-15 18:33:36.45246+02
(11 rows)

$ select id, lastid
  from (
    select id,
    t-lag(t) over (order by t) as lag,
    lag(id) over (order by t) as lastid from test
  ) as _
  where lag < '1 second';
 id | lastid 
----+--------
 10 |      9
 11 |     10
 12 |     11
 13 |     12
 14 |     13
 15 |     14
 16 |     15
 19 |     18
(8 rows)