webentwicklung-frage-antwort-db.com.de

Ergebnisse zwischen zwei Terminen in PostgreSQL abrufen

Ich habe folgende Tabelle:

+-----------+-----------+------------+----------+
| id        | user_id   | start_date | end_date |
| (integer) | (integer) | (date)     | (date)   |
+-----------+-----------+------------+----------+

Die Felder start_date und end_date enthalten Datumswerte wie YYYY-MM-DD. 

Ein Eintrag aus dieser Tabelle kann folgendermaßen aussehen: (1, 120, 2012-04-09, 2012-04-13).

Ich muss eine Abfrage schreiben, die alle Ergebnisse eines bestimmten Zeitraums abrufen kann. 

Das Problem ist, dass wenn ich Ergebnisse von 2012-01-01 nach 2012-04-12 abrufen möchte, ich 0 Ergebnisse bekomme, obwohl es einen Eintrag mit start_date = "2012-04-09" und end_date = "2012-04-13" gibt.

sqlpostgresqldateoverlap
27
Psyche
 SELECT *
   FROM mytable
  WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE);

Datetime-Funktionen ist der relevante Abschnitt in den Dokumenten.

48
Marco Mariani

Angenommen, Sie möchten alle " überlappenden " Zeiträume, d. H. Alle, die mindestens einen Tag gemeinsam haben.

Stellen Sie sich Zeiträume auf einer geraden Linie vor und bewegen Sie sie vor Ihren Augen, und Sie werden die notwendigen Bedingungen sehen .

_SELECT *
FROM   tbl
WHERE  start_date <= '2012-04-12'::date
AND    end_date   >= '2012-01-01'::date;
_

Dies ist für mich manchmal schneller als OVERLAPS - was der andere gute Weg ist, dies zu tun (wie @ Marco bereits bereitgestellt ).

Beachten Sie den subtilen Unterschied ( pro Dokumentation ):

OVERLAPS nimmt automatisch den früheren Wert des Paares als Start. Jede Zeitspanne entspricht dem halboffenen Intervall start <= time < end, sofern Anfang und Ende nicht gleich sind. In diesem Fall entspricht dies der einzelnen Zeitspanne sofortig. Dies bedeutet zum Beispiel, dass sich zwei Zeiträume mit nur einem gemeinsamen Endpunkt nicht überlappen.

Meine kühne Betonung.

Performance

Bei großen Tabellen kann der richtige Index die Leistung ( stark verbessern ).

_CREATE INDEX tbl_date_inverse_idx ON tbl(start_date, end_date DESC);
_

Möglicherweise mit einer anderen (führenden) Indexspalte, wenn Sie zusätzliche selektive Bedingungen haben.

Beachten Sie die umgekehrte Reihenfolge der beiden Spalten. Ausführliche Erklärung:

29
Erwin Brandstetter

hatte gerade die gleiche Frage und antwortete auf diese Weise, ob das helfen könnte.

select * 
from table
where start_date between '2012-01-01' and '2012-04-13'
or    end_date   between '2012-01-01' and '2012-04-13'
17
Chris

Wenn Sie eine Abfrage in beliebigen Gebietsschemaeinstellungen verwenden möchten, beachten Sie Formatierung des Datums Sie:

SELECT * 
  FROM testbed 
 WHERE start_date >= to_date('2012-01-01','YYYY-MM-DD')
   AND end_date <= to_date('2012-04-13','YYYY-MM-DD');
1
vyegorov

Wenn ich mir die Daten anschaue, bei denen es nicht funktioniert - bei denen der Tag weniger als oder gleich 12 beträgt - frage ich mich, ob es die Daten im Format JJJJ-TT-MM analysiert?

0
Edmund
SELECT *
FROM ecs_table
WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE + interval '1');
0
user9251288

Sie müssen die Methode zum Abrufen von Datumsteilen verwenden:

SELECT * FROM testbed WHERE start_date  ::date >= to_date('2012-09-08' ,'YYYY-MM-DD') and date::date <= to_date('2012-10-09' ,'YYYY-MM-DD')
0
boopathiraja

Nichts für ungut, aber um die Performance von SQL zu überprüfen, habe ich einige der oben genannten Lösungen ausgeführt. 

Lassen Sie mich Ihnen Statistiken über Top-3-Lösungsansätze mitteilen, die mir begegnen.

1) Took: 1,58 MS Durchschn

2) Took: 2,87 MS Durchschn

3) Took: 3,95 MS Durchschn

Versuchen Sie jetzt folgendes: 

 SELECT * FROM table WHERE DATE_TRUNC('day', date ) >= Start Date AND DATE_TRUNC('day', date ) <= End Date

Nun nahm diese Lösung: 1.61 Avg.

Und die beste Lösung ist 1. die von marco-mariani

0
rxpande