Encuentre rangos de fechas superpuestos en PostgreSQL

Solución:

La respuesta actualmente aceptada no responde a la pregunta. Y está mal en principio. a BETWEEN x AND y se traduce en:

a >= x AND a <= y

Incluso el límite superior, mientras que la gente normalmente necesita excluir eso:

a >= x AND a < y

Con fechas se puede ajustar fácilmente. Para el año 2009, utilice ‘2009-12-31’ como límite superior.
Pero no es tan simple con marcas de tiempo que permiten dígitos fraccionarios. Las versiones modernas de Postgres utilizan internamente un entero de 8 bytes para almacenar hasta 6 fracciones de segundo (resolución µs). Sabiendo esto nosotros podría aún lo hace funcionar, pero eso no es intuitivo y depende de un detalle de implementación. Mala idea.

Es más, a BETWEEN x AND y no encuentra rangos superpuestos. Nosotros necesitamos:

b >= x AND a < y

Y jugadores que nunca se fue todavía no se tienen en cuenta.

Respuesta adecuada

Asumiendo el año 2009, Reformularé la pregunta sin cambiar su significado:

“Encuentra a todos los jugadores de un equipo que se unieron antes de 2010 y no se fueron antes de 2009”.

Consulta básica:

SELECT p.*
FROM   team     t
JOIN   contract c USING (name_team) 
JOIN   player   p USING (name_player) 
WHERE  t.name_team = ? 
AND    c.date_join  <  date '2010-01-01'
AND    c.date_leave >= date '2009-01-01';

Pero hay más:

Si la integridad referencial se aplica con restricciones FK, la tabla team en sí mismo es solo ruido en la consulta y se puede eliminar.

Si bien el mismo jugador puede irse y volver a unirse al mismo equipo, también necesitamos doblar posibles duplicados, por ejemplo, con DISTINCT.

Y nosotros mayo Necesito prever un caso especial: jugadores que nunca se fueron. Suponiendo que esos jugadores tengan NULL en date_leave.

“Se supone que un jugador del que no se sabe que se haya ido está jugando para el equipo hasta el día de hoy”.

Consulta refinada:

SELECT DISTINCT p.* 
FROM   contract c
JOIN   player   p USING (name_player) 
WHERE  c.name_team = ? 
AND    c.date_join  <  date '2010-01-01'
AND   (c.date_leave >= date '2009-01-01' OR c.date_leave IS NULL);

La precedencia del operador trabaja en nuestra contra, AND se une antes OR. Necesitamos paréntesis.

Respuesta relacionada con optimizado DISTINCT (si los duplicados son comunes):

• Tabla de muchos a muchos: el rendimiento es malo

Típicamente, nombres de las personas físicas no son únicas y se utiliza una clave primaria sustituta. Pero, obviamente, name_player es la clave principal de player. Si todo lo que necesitas son los nombres de los jugadores, no necesitamos la mesa. player en la consulta, ya sea:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    date_join  <  date '2010-01-01'
AND   (date_leave >= date '2009-01-01' OR date_leave IS NULL);

SQL OVERLAPS operador

El manual:

OVERLAPS toma automáticamente el valor anterior del par como inicio. Se considera que cada período de tiempo representa el intervalo semiabierto start <= time < end, a no ser que start y end son iguales, en cuyo caso representa ese instante de tiempo único.

Para cuidar el potencial NULL valores, COALESCE parece más fácil:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    (date_join, COALESCE(date_leave, CURRENT_DATE)) OVERLAPS
       (date '2009-01-01', date '2010-01-01');  -- upper bound excluded

Tipo de rango con soporte de índice

En Postgres 9.2 o posterior también puede operar con tipos de rango:

SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    daterange(date_join, date_leave) &&
       daterange '[2009-01-01,2010-01-01)';  -- upper bound excluded

Los tipos de rango añaden algo de sobrecarga y ocupan más espacio. 2 x date = 8 bytes; 1 x daterange = 14 bytes en disco o 17 bytes en RAM. Pero en combinación con el operador de superposición && la consulta se puede admitir con un índice GiST.

Además, no es necesario aplicar valores NULL en casos especiales. NULL significa “rango abierto” en un tipo de rango, exactamente lo que necesitamos. La definición de la tabla ni siquiera tiene que cambiar: podemos crear el tipo de rango sobre la marcha y respaldar la consulta con un índice de expresión coincidente:

CREATE INDEX mv_stock_dr_idx ON mv_stock USING gist (daterange(date_join, date_leave));

Relacionado:

• Tabla de historial de acciones promedio

¿Por qué no usar entre sin la parte de la fecha?

WHERE datefield BETWEEN '2009-10-10 00:00:00' AND '2009-10-11 00:00:00'

¿o algo así?

---

---

Utiliza Nuestro Buscador