Muchas veces nos encontramos con la necesidad de realizar cálculos con fechas, y siempre son problemáticas. Ya hablamos en otra ocasión sobre Gestión de Husos Horarios en PostgreSQL y hoy vamos a ver algunas funcionalidades que nos ofrece PostgreSQL para movernos con fechas.
CURRENT_DATE y now()
La funcione <current_date> nos permite saber la fecha actual, sin embargo, no sabemos la hora ni su desplazamiento -ya que es un tipo date-.
Si utilizamos la función <now()> obtendremos la fecha-hora actual con su desplazamiento horario, por ejemplo
search=> select current_date;También podemos utilizar <current_timestamp> que, a efectos prácticos, es lo mismo que <now()>, por ejemplo
date
------------
2010-11-05
(1 row)
search=> select now();
now
-------------------------------
2010-11-05 09:14:18.450761+01
(1 row)
search=> select current_timestamp;
now
-------------------------------
2010-11-05 09:18:13.745486+01
(1 row)
search=> select now(), current_timestamp;
now | now
-------------------------------+-------------------------------
2010-11-05 09:18:27.408072+01 | 2010-11-05 09:18:27.408072+01
(1 row)
Uso de Intervalos
Hasta aquí nada nuevo, sin embargo, si quiero obtener la fecha de ayer, cómo lo hago? Bueno, existen dos formas de hacerlo: bien y no tan bien. Hoy os explicaré la forma de hacerlo bien ... :D
PostgreSQL nos ofrece la posibilidad de utilizar Intervalos, por ejemplo, <hour>, <day>, <month> -que corresponden con hora, día, mes, etc- por lo tanto, como puedo utilizar funciones de suma y resta a las fechas, puedo hacer:
search=> select now()-'24 hour'::interval;He restado un intervalo de 24 horas a la fecha actual, por lo tanto a alguien se le puede estar ocurriendo la siguiente solución para saber la fecha de ayer:
?column?
-------------------------------
2010-11-04 09:24:12.504315+01
(1 row)
search=> select (now()-'24 hour'::interval)::date;Bien, esta solución es válida pero no es la correcta. Para saber la fecha de ayer PostgreSQL nos ofrece la siguiente opción, utilizar <yesterday>, por ejemplo:
date
------------
2010-11-04
(1 row)
search=> select 'yesterday'::date;O mejor aún, con TimeZone
date
------------
2010-11-04
(1 row)
search=> select 'yesterday'::timestamp with time zone;También podemos saber la fecha de hoy utilizando
timestamptz
------------------------
2010-11-04 00:00:00+01
(1 row)
search=> select 'today'::date;
date
------------
2010-11-05
(1 row)
Como veís el uso de intervalos -usando casting- nos puede ahorras muchas horas de sufrimiento, simplemente debemos recordar que para poder sumar o restar a <now()> debemos utilizar <interval> y, que si queremos saber la fecha de hoy o ayer, podemos utilizar las palabras <today> y <yesterday> haciendo un cast a <date> o <timestamp>.
Por ejemplo, en una de mis aplicaciones existe una tabla de eventos que guarda un registro de lo que ha sucedido, si quisiera saber cuántos eventos se han producido desde ayer utilizaría:
search=> select count(1) from audit.events where event_datetime > 'yesterday'::timestamp with time zone;Sin embargo, si quiero saber cuántos eventos se han producido en las últimas 24h, haría
count
-------
38075
(1 row)
search=> select count(1) from audit.events where event_datetime > now()-'24 hour'::interval;El resultado es diferente -como tiene que ser- porque si analizamos las fechas vemos que al utilizar <yesterday> siempre comenzamos desde las 00:00h, y con los intervalos dependemos de la hora en la que se ejecute
count
-------
37493
(1 row)
search=> select 'yesterday'::timestamp with time zone, now()-'24 hour'::interval;Para concluir, os contaré un uso de un intervalo que, realmente a mí, me ha sacado de muchos apuros: week, month, year (semana, mes, año).
timestamptz | ?column?
------------------------+-------------------------------
2010-11-04 00:00:00+01 | 2010-11-04 09:52:30.401646+01
(1 row)
Fijaros cómo PostgreSQL se encarga de ajustar los cambios horarios. En España hemos realizado recientemente el cambio de hora, y por lo tanto, hace una semana estábamos con GMT+2, pero ahora estamos con GMT+1.
search=> select now()-'1 year'::interval;Sólo una última cosa, el uso de nombre ne singular o plurar es el mismo, por ejemplo, <week> y <weeks>
?column?
-------------------------------
2009-11-05 09:58:15.050784+01
(1 row)
search=> select now()-'1 month'::interval;
?column?
-------------------------------
2010-10-05 09:58:25.412129+02
(1 row)
search=> select now()-'1 week'::interval;
?column?
------------------------------
2010-10-29 09:58:31.60385+02
(1 row)
search=> select now()-'2 week'::interval;
?column?
-------------------------------
2010-10-22 09:59:20.024957+02
(1 row)
search=> select now()-'2 weeks'::interval;
?column?
-------------------------------
2010-10-22 09:59:22.601622+02
(1 row)
Conclusiones
PostgreSQL nos ofrece un montón de ventajas en el manejo de Fechas -con o sin intervalos- además de proporcionarnos un interface sencillo. Es cierto que muchas veces -por descuido- olvidamos el uso de los desplazamientos horarios, pero son vitales y, si entramos en el mundo de la auditoría de sistemas, no podemos olvidarlos.
Referencias
Tengo una base de datos con reporte de ventas diarias necesito agrupar por fecha que solo me muestre el acumulado por meses... como se puede armar la instrucción...?
ResponderEliminaruna pregunta tengo un campo timestamp que me guarda fecha y hora.. lo que quiero es usar un intervalo para ese campo restringiendo unas horas en la que no se puede ejecutar una consulta que quiero... es decir quiero con el intervalo decir que me retorne algo dentro de la Hora a a la hora b, como usaria el intervalo
ResponderEliminarhola compañeros una pregunta tengo un loop que me recorre una tabla y necesito saber si la fecha que esta pasando esta dentro de la ultima semana del mes y si es asi cuanto dias tiene esa semana un ejemplo si la mes se termino el diamrtes de esa seman para guardarlo en la tabla a en caso contrario lo guardo enla tabla b
ResponderEliminarMuy buena información! gracias por compartila y Saludos desde México.
ResponderEliminar