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)