Generación de series temporales entre dos fechas en PostgreSQL

Puede generar series directamente con fechas. No es necesario utilizar ints o marcas de tiempo:

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

Hay dos respuestas (hasta ahora). Ambos funcionan, pero ambos son subóptimos. He aquí 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 reparto a date (day::date) lo hace implícitamente.

• Pero tampoco tiene sentido lanzar literales de fecha a date como parámetro de entrada. Au contraire, timestamp es la mejor opción aquí. La ventaja en el rendimiento es pequeña, pero no hay razón para no tomarla. Y usted no implica innecesariamente DST reglas acopladas con el tipo de datos timestamp with time zone. Véase 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 set-returning en la lista SELECT:

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

La palabra clave AS es requerida aquí, ya que el alias de la columna day se malinterpretaría de otra manera.

Yo no aconsejaría usar la última antes de Postgres 10, al menos no con más de una función de retorno de conjuntos en la misma lista SELECT. Véase:

• ¿Cuál es el comportamiento esperado para múltiples funciones de retorno de conjuntos en la cláusula select?

¿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 tomar y devolver numeric se añadió con Postgres 9.5. Pero los únicos relevantes aquí son los dos últimos en negrita tomando y regresando timestamp / timestamptz.

As puedes ver, no hay ninguna variante que tome o devuelva date. Es por eso que necesitamos un cast explícito si queremos devolver date. Pasar timestamp resuelve a la función derecha directamente sin tener que descender a las reglas de resolución de tipo de función y sin conversión adicional para la entrada.

Y timestamp '2004-03-07' es perfectamente válido. La parte de tiempo por defecto es 00:00 si se omite.

Gracias a tipo de función resolución todavía podemos pasar date. Pero eso requiere más trabajo de Postgres. Hay una implicit cast from date to timestamp as well as from date to timestamptz. Sería ambiguo, pero timestamptz es "preferido" entre los "tipos de Fecha/hora". Así que la coincidencia se decide en el paso 4d.:

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

Además del trabajo extra en la resolución de tipo de función, esto agrega un molde adicional a timestamptz. El reparto de timestamptz no solo agrega más costo, sino que también puede introducir problemas con el horario de verano (DST) que conducen a resultados inesperados en casos raros. (DST es un concepto estúpido, 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?

He añadido demos al violín para mostrar el plan de consulta más caro:

dbfiddle aquí

Relacionado:

• ¿Hay una manera de desactivar la sobrecarga de funciones en Postgres
• Generar series de fechas-usando el tipo de fecha como entrada
• Postgres data type cast