[postgreSQL] Trabajando con fechas y generate_series

Un amigo me preguntó ¿cómo se hace en postgreSQL para obtener todos los días específicos de un mes?, ejemplo, “todos los días del primero al último en secuencia” o “el último día específico”, o “¿cuándo cae el black-friday en amazon este año?” o cosas como “¿cuántos lunes tuvo este mes?, debido a que llevaba mucho tiempo sin trabajar con postgreSQL, la pregunta me llevó por refrescar un poco …

Ahí recordé generate_series!

Una de las virtudes de “generate_series” es que nos permite obtener un conjunto consecutivo de valores enteros desde un valor “A”, a un valor “B” de manera muy sencilla:

SELECT generate_series(0,5)

Salida:

0
1
2
3
4
5

Esto, inicialmente no tiene mucho sentido asociado a trabajar con fechas, pero cuando te das cuenta que puedes operarlas vía intervalos y colocarlas en una consulta en el FROM, encontrarás cosas muy útiles cómo:

SQL: Obteniendo las fechas de cada día del mes en postgreSQL:

SELECT d.date, EXTRACT('month' from d.date) FROM
(SELECT generate_series(0,30) + first_day_month('2012-02-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-02-21'::date))

Nota: first_day_month es una función propia que retorna el primer día de un mes específico, lo pueden obtener con:

SELECT DATE_TRUNC('month', '2012-02-21'::date)

Como ven, la consulta genera un vector en el FROM, de una serie generada de sumar desde 0 al 30 a cada fecha desde la primera fecha del mes, si a ese vector le aplicamos un "WHERE" donde sólo me muestre los días que su MES coincida con el mes del primer día, tienes la función correcta que te retorna todas las fechas de un mes específico.

¿Cuántos lunes tiene el mes?

Para saber, vía "generate_series" las fechas en que cayeron todos los lunes del mes específico sería un simple "WHERE" contra la consulta anterior:

SELECT d.date, EXTRACT('month' from d.date) FROM
(SELECT generate_series(0,30) + first_day_month('2012-02-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-02-21'::date))
AND EXTRACT('dow'from d.date) = '1'

Verificando que "dow" (DAY OF WEEK") es igual a 1 (Lunes).

Contarlos, es simplemente reemplazar el campo por un COUNT (GROUP implícito):

SELECT count(d.date) as n_lunes FROM
(SELECT generate_series(0,30) + first_day_month('2012-07-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-07-21'::date))
AND EXTRACT('dow'from d.date) = '1'

Y Julio tiene 5 lunes ...

Computando fechas específicas

El Black-Friday (día interesante para los geeks que aprovechamos comprar cosas en Amazon xD) es una interesante fecha para calcular, es el día siguiente al "Día de Acción de Gracias" que son el cuarto jueves de Noviembre (el "black-friday" sería el 4 viernes de noviembre):

SELECT d.date FROM
(SELECT generate_series(0,30) + first_day_month('2012-11-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-11-21'::date))
AND EXTRACT('dow'from d.date) = '5' AND ((EXTRACT('day' from d.date)::integer+1)/7) = 3

Resultando que el "Black-friday" caerá el "2012-11-23".

Claro, que simplemente podrías enumerar las semanas usando una función "ventana" (window functions) >

SELECT ROW_NUMBER() OVER (ORDER BY d.date) as position, d.date FROM
(SELECT generate_series(0,30) + first_day_month('2012-11-21'::date)::date as date) d
WHERE EXTRACT('month' from d.date) = EXTRACT('month' from first_day_month('2012-11-21'::date))
AND EXTRACT('dow'from d.date) = '5'

Y esto retorna:

1   |   "2012-11-02"
2   |  "2012-11-09"
3   |  "2012-11-16"
4   |  "2012-11-23"
5   |  "2012-11-30"

Conclusiones

Uno de los grandes problemass a la hora de utilizar un RDBMS, es tratar de mantenerse "genérico", sin pensar en utilizar funciones específicas del lenguaje que podrían optimizar nuestro trabajo al máximo, de hecho, en Oracle la mayoría de los ejemplos acá presentes se hacen con PL/SQL o con cláusulas WITH más complejas que nuestra corta función SQL usando "generate_series".

About these ads

Publicado el 7 julio 2012 en Blogeando!, Cultura Libre, Databases, La nota del día, PlanetaLinux, Software Libre. Añade a favoritos el enlace permanente. 6 comentarios.

  1. y ahora mysql 5.5 porta muchas funciones de oracle, el jueves carge un diseño actualizado y no cargo en el 5.3 (o mariadb 5.3) .. especialmetne en la compatibilidad de lcaves primarias combinadas, oracle ya esta fastidiando mucho..

    escuche alguna vez que postgresql y oracle compitieron en un concurso donde postgresql aplasto oracle, despues de esto unos meses despues oracle le corto el acceso a los servidores de pruebas para sparc/sun de las release de estos de posgresql.. pero no encunentro data alguna..

  2. Esto se puede realizar en oracle?

  1. Pingback: [postgreSQL] Trabajando con fechas y generate_series | Phenobarbital con Soda! « Aguilared

  2. Pingback: [postgreSQL] Trabajando con fechas y generate_series « Cursos en SmartDreams.cl

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 2.970 seguidores

%d personas les gusta esto: