Generando series de tiempo entre dos fechas en PostgreSQL

Poseemos la mejor solución que hallamos online. Deseamos que te sea de mucha utilidad y si deseas comentarnos algo que nos pueda ayudar a crecer hazlo con total libertad.

Solución:

Se puede hacer sin conversión a / desde int (pero a / desde la marca de tiempo en su lugar)

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

Para generar una serie de fechas este es el óptimo camino:

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

• Adicional date_trunc() no es necesario. El elenco de date (day::date) lo hace implícitamente.

• Pero tampoco tiene sentido emitir literales de fecha para date como parámetro de entrada. Au contraire, timestamp es la mejor opcion. La ventaja en el rendimiento es pequeña, pero no hay razón para no aprovecharla. Y no implica innecesariamente las reglas de DST (horario de verano) junto con la conversión de date para timestamp with time zone y de regreso. Vea abajo.

Sintaxis corta equivalente, menos explícita:

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

O con la función de devolución de conjuntos en el SELECT lista:

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

los AS la palabra clave es requerido en la última variante, Postgres malinterpretaría el alias de la columna day de lo contrario. Y yo quisiera no aconsejar esa variante antes de Postgres 10, al menos no con más de una función de retorno de conjunto en el mismo SELECT lista:

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

(Aparte de eso, la última variante suele ser la más rápida por un pequeño margen).

Por qué timestamp [without time zone]?

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

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

(numeric Se agregaron variantes con Postgres 9.5.) Las relevantes son las dos últimas en negrita tomando y regresando timestamp / timestamptz.

Hay sin variante tomando o devolviendo date. Se necesita un elenco explícito para regresar date. La llamada con timestamp Los argumentos se resuelven directamente en la mejor variante sin descender a las reglas de resolución de tipo de función y sin conversión adicional para la entrada.

timestamp '2004-03-07' es perfectamente válido, por cierto. La parte de tiempo omitida tiene como valor predeterminado 00:00 con formato ISO.

Gracias a resolución del tipo de función todavía podemos pasar date. Pero eso requiere más trabajo de Postgres. Hay un implícito emitir de date para timestamp así como uno de date para timestamptz. Sería ambiguo, pero timestamptz es “privilegiado” entre los “tipos de fecha / hora”. Entonces el partido se decide al paso 4d.:

Revise todos los candidatos y mantenga los 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á la conversión de tipo. Conserve todos los candidatos si ninguno acepta los tipos preferidos. Si solo queda un candidato, utilícelo; de lo contrario, continúe con el siguiente paso.

Además del trabajo adicional en la resolución del tipo de función, esto agrega un elenco adicional a timestamptz – lo que no solo agrega más costos, sino que también puede presentar problemas con el horario de verano que conducen a resultados inesperados en casos excepcionales. (El horario de verano es un concepto estúpido, por cierto, no puedo enfatizar esto lo suficiente).

• ¿Cómo genero una serie de fechas en PostgreSQL?
• ¿Cómo genero una serie de tiempo en PostgreSQL?

Agregué demostraciones al violín que muestran el plan de consulta más caro:

db <> violín aquí

Relacionado:

• ¿Hay alguna forma de deshabilitar la sobrecarga de funciones en Postgres?
• Genere series de fechas, utilizando el tipo de fecha como entrada
• Conversión de tipos de datos de Postgres

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;

Agradecemos que quieras avalar nuestro ensayo fijando un comentario o dejando una valoración te damos la bienvenida.

---

---

Utiliza Nuestro Buscador