libros - postgresql introduccion

Postgresql selecciona entre rango de mes

Question

Tengo una tabla con una de las columnas como fecha en el formato ''AAAA-MM-DD''. ¿Puedo usar seleccionar para obtener todos los datos en un rango mensual? Supongamos que quiero todos los datos desde 2012-01-xx hasta 2013-04-xx. Así que básicamente estoy buscando una consulta SQL como la que se muestra a continuación:

SELECT * FROM table WHERE date IN BETWEEN ''2012-01'' AND ''2013-04'' (INVALID QUERY)

Como todos los meses comienzan con ''01'', puedo modificar la consulta anterior para ajustar la condición de inicio.

SELECT * FROM table WHERE date IN BETWEEN ''2012-01-01'' AND ''2013-04'' (INVALID QUERY)

Ahora el problema viene con la fecha de finalización. Tengo que calcular manualmente la última fecha para el mes dado, tomando todos los factores en cuenta como la duración del mes, año bisiesto, etc., ya que la consulta falla si la fecha dada no es válida. Así que actualmente estoy haciendo algo como esto:

SELECT * FROM table WHERE date IN BETWEEN ''2012-01-01'' AND ''VALID_MONTH_END_DATE'' (VALID Query)

Quiero saber si hay alguna forma de evitar este cálculo de fecha de finalización válido.

Aclaración

Lo pensé antes del primer día del mes siguiente, pero incluso entonces tendré que aplicar un poco de lógica, si es diciembre, el próximo mes sería enero del próximo año. Quería saber si una solución SQL solo es posible?

Answer 1

Es bueno evitar BETWEEN comparaciones del rango de fechas. Mejor uso >= y < ya que funciona igualmente con columnas / valores de fecha y fecha y hora.

Una forma (si puedes construir las fechas externamente):

WHERE date >= DATE ''2012-01-01''
AND date < DATE ''2013-05-01'' --- first date of the next month

También puedes usar la aritmética de fechas:

WHERE date >= DATE ''2012-01-01''
AND date < DATE (''2013-04-01'' + INTERVAL ''1 MONTH'')

o el operador OVERLAPS :

WHERE (date, date) OVERLAPS
(DATE ''2012-01-01'', DATE ''2013-05-01'')

También debe leer la documentación de Postgres: Funciones de fecha / hora y operadores

El manual explica aquí por qué OVERLAPS funciona de esta manera:

Cada período de tiempo se considera que representa el intervalo de apertura parcial <= tiempo <fin, a menos que el inicio y el final sean iguales, en cuyo caso representa ese instante de tiempo único. Esto significa, por ejemplo, que dos períodos de tiempo con solo un punto final en común no se superponen.

Answer 2

Esta es una necesidad muy común en los entornos de informes. He creado varias funciones para acomodar estas manipulaciones de fecha

CREATE OR REPLACE FUNCTION public.fn_getlastofmonth (
date
)
RETURNS date AS
$body$
begin
return (to_char(($1 + interval ''1 month''),''YYYY-MM'') || ''-01'')::date - 1;
end;
$body$
LANGUAGE ''plpgsql''
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Entonces puedes usar ...

WHERE date >= ''2012-01-01''
AND date < fn_getlastofmonth(''2013-04-01'') 

Answer 3

No lo he probado pero vale la pena intentarlo

SELECT *
FROM some_table
WHERE some_date
BETWEEN ''2012-01-01'' AND date(''2013-04-01'') - integer ''1''

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

Answer 4

Todas las respuestas anteriores proporcionan una solución de trabajo pero están incompletas de una forma u otra. Como estaba buscando una solución solo de SQL (sin función), estoy combinando los mejores consejos de las soluciones anteriores.

La solución ideal para mi pregunta sería:

SELECT * FROM table
WHERE date >= ''2012-01-01'' AND date < date(''2013-04-01'') + interval ''1 month''

EDITAR

No estoy utilizando la función de superposición aquí porque estoy pasando los valores de fecha predeterminados para el inicio y el final como ''época'' y ''ahora''. Si el usuario no ha especificado ningún rango de tiempo, la consulta se convierte en:

SELECT * FROM table
WHERE date >= ''epoch'' AND date < ''now''

La función de solapamiento no puede manejar ''época'' y ''ahora'' y proporciona un error de SQL mientras que el código anterior funciona perfectamente para ambos casos.

PD: He votado a favor y en contra de todas las respuestas correctas de alguna manera y me condujo a esta solución.

Answer 5

SET search_path=tmp;
DROP TABLE zdates;
CREATE TABLE zdates
( zdate timestamp NOT NULL PRIMARY KEY
, val INTEGER NOT NULL
);
-- some data
INSERT INTO zdates(zdate,val)
SELECT s, 0
FROM generate_series(''2012-01-01'', ''2012-12-31'', ''1 day''::interval ) s
;
UPDATE zdates
SET val = 1000 * random();
DELETE FROM zdates
WHERE random() < 0.1;
-- CTE to round the intervals down/up to the begin/end of the month
WITH zope AS (
SELECT date_trunc(''month'', zdate)::date AS zbegin
, date_trunc(''month'', zdate+interval ''1 month'')::date AS zend
, val AS val
FROM zdates
)
SELECT z.zbegin
, z.zend
, COUNT(*) AS zcount
, SUM(val) AS zval
FROM zope z
GROUP BY z.zbegin, z.zend
ORDER BY z.zbegin, z.zend
;

RESULTADO:

CREATE TABLE
INSERT 0 366
UPDATE 366
DELETE 52
zbegin | zend | zcount | zval
------------+------------+--------+-------
2012-01-01 | 2012-02-01 | 28 | 13740
2012-02-01 | 2012-03-01 | 28 | 14923
2012-03-01 | 2012-04-01 | 26 | 13775
2012-04-01 | 2012-05-01 | 25 | 11880
2012-05-01 | 2012-06-01 | 25 | 12693
2012-06-01 | 2012-07-01 | 25 | 11082
2012-07-01 | 2012-08-01 | 26 | 13254
2012-08-01 | 2012-09-01 | 28 | 13632
2012-09-01 | 2012-10-01 | 28 | 16461
2012-10-01 | 2012-11-01 | 23 | 12622
2012-11-01 | 2012-12-01 | 24 | 12554
2012-12-01 | 2013-01-01 | 28 | 14563
(12 rows)