con - Generando series de tiempo entre dos fechas en PostgreSQL

comparar fechas en postgresql

Question

Tengo una consulta como esta que bien genera una serie de fechas entre 2 fechas determinadas:

select date ''2004-03-07'' + j - i as AllDate
from generate_series(0, extract(doy from date ''2004-03-07'')::int - 1) as i,
generate_series(0, extract(doy from date ''2004-08-16'')::int - 1) as j

Genera 162 fechas entre 2004-03-07 y 2004-08-16 y esto es lo que quiero. El problema con este código es que no daría la respuesta correcta cuando las dos fechas son de años diferentes, por ejemplo, cuando intento 2007-02-01 y 2008-04-01 .

¿Hay una mejor solución?

Answer 1

Hay dos respuestas (hasta ahora). Ambos funcionan, pero ambos son subóptimos. Aquí hay un tercero:

SELECT day::date
FROM generate_series(timestamp ''2004-03-07''
, timestamp ''2004-08-16''
, interval ''1 day'') day;

• No es necesario un date_trunc() adicional. El lanzamiento hasta la date ( day::date ) lo hace implícitamente.

• Pero tampoco tiene sentido convertir los literales de date hasta la date como parámetro de entrada. Au contraire, timestamp es la mejor opción aquí. La ventaja en rendimiento es pequeña, pero no hay razón para no tomarla. Y no implica innecesariamente reglas de horario de verano junto con la timestamp with time zone tipo de datos timestamp with time zone . Vea la explicación a continuación.

Equivalente más corto:

SELECT day::date
FROM generate_series(timestamp ''2004-03-07'', ''2004-08-16'', ''1 day'') day;

O incluso con la función de ajuste de retorno en la lista SELECT :

SELECT generate_series(timestamp ''2004-03-07'', ''2004-08-16'', ''1 day'')::date AS day;

La palabra clave AS se requiere aquí, ya que el day alias de la columna no se entendería de otra forma.

No aconsejaría utilizar el último antes de Postgres 10, al menos no con más de una función de retorno de conjunto en la misma lista SELECT . Ver:

• ¿Cuál es el comportamiento esperado para las funciones de devolución de conjuntos múltiples en la cláusula de selección?

¿Por qué?

Hay una serie de variantes sobrecargadas de generate_series() . Actualmente (Postgres 10):

SELECT oid::regprocedure AS function_signature
, prorettype::regtype AS return_type
FROM pg_proc
where proname = ''generate_series'';

function_signature | return_type :-------------------------------------------------------------------------------- | :-------------------------- generate_series(integer,integer,integer) | integer generate_series(integer,integer) | integer generate_series(bigint,bigint,bigint) | bigint generate_series(bigint,bigint) | bigint generate_series(numeric,numeric,numeric) | numeric generate_series(numeric,numeric) | numeric generate_series(timestamp without time zone,timestamp without time zone,interval) | timestamp without time zone generate_series(timestamp with time zone,timestamp with time zone,interval) | timestamp with time zone

La variante de toma y devolución numeric se agregó con Postgres 9.5. Pero los únicos relevantes aquí son los dos últimos en tomar negrita y devolver timestamp / timestamptz .

Como puede ver, no hay una variante que tome o regrese la date . Es por eso que necesitamos un lanzamiento explícito si queremos devolver la date . Pasar la timestamp resuelve directamente a la función correcta sin tener que descender a reglas de resolución de tipo de función y sin conversión adicional para la entrada.

Y la timestamp ''2004-03-07'' es perfectamente válida. La parte de tiempo predeterminada es 00:00 si se omite.

Gracias a la resolución del tipo de función aún podemos pasar la date . Pero eso requiere más trabajo de Postgres. Hay un elenco implícito desde la date hasta la timestamp de timestamp , así como desde la date a la timestamptz . Sería ambiguo, pero timestamptz es "preferido" entre "Tipos de fecha / hora". Entonces el partido se decide en el paso 4d. :

Ejecute todos los candidatos y mantenga aquellos que aceptan tipos preferidos (de la categoría de tipo de datos de entrada) en la mayoría de las posiciones donde se requerirá la conversión de tipo. Mantenga todos los candidatos si ninguno acepta los tipos preferidos. Si solo queda un candidato, úsalo; de lo contrario, continúe con el siguiente paso.

Además del trabajo adicional en la resolución de tipo de función, esto agrega un lanzamiento extra a timestamptz . La conversión a timestamptz no solo agrega más costo, sino que también puede presentar problemas con el horario de verano (DST), lo que lleva a resultados inesperados en casos excepcionales. (DST es un concepto tonto, por cierto, no puedo enfatizar esto lo suficiente). Relacionado:

• ¿Cómo puedo generar una serie de fechas en PostgreSQL?
• ¿Cómo puedo generar una serie temporal en PostgreSQL?

Agregué demos al violín para mostrar el plan de consulta más caro:

dbfiddle here

Relacionado:

• ¿Hay alguna forma de desactivar la sobrecarga de funciones en Postgres?
• Generar series de fechas, utilizando el tipo de fecha como entrada
• Tipo de datos Postgres emitidos

Answer 2

Puedes generar series directamente con fechas. No es necesario usar ints o timestamps:

select date::date
from generate_series(
''2004-03-07''::date,
''2004-08-16''::date,
''1 day''::interval
) date;

Answer 3

Se puede hacer sin conversión a / desde int (pero a / desde timestamp en su lugar)

SELECT date_trunc(''day'', dd):: date
FROM generate_series
( ''2007-02-01''::timestamp
, ''2008-04-01''::timestamp
, ''1 day''::interval) dd
;