| BD: MySQL
Autor: MySQL AB
Traductor: MySQL Hispano
Pregunta: Calculo con fechas
Respuesta:
MySQL proporciona diversas funciones que se pueden usar para efectuar cálculos sobre fechas, por ejemplo, para calcular edades o extraer partes de una fecha (día, mes, año, etc).
Para determinar la edad de cada una de nuestras mascotas, tenemos que calcular la diferencia de años de la fecha actual y la fecha de nacimiento, y entonces substraer uno si la fecha actual ocurre antes en el calendario que la fecha de nacimiento. Las siguientes consultas muestran la fecha actual, la fecha de nacimiento y la edad para cada mascota.
mysql> SELECT nombre, nacimiento, CURRENT_DATE,
-> (YEAR(CURRENT_DATE) - YEAR(nacimiento))
-> - (RIGHT(CURRENT_DATE,5) < RIGHT(nacimiento,5)) AS edad FROM mascotas;
+--------+------------+--------------+------+
| nombre | nacimiento | CURRENT_DATE | edad |
+--------+------------+--------------+------+
| Fluffy | 1999-02-04 | 2002-12-23 | 3 |
| Mau | 1998-03-17 | 2002-12-23 | 4 |
| Buffy | 1999-05-13 | 2002-12-23 | 3 |
| FanFan | 2000-08-27 | 2002-12-23 | 2 |
| Kaiser | 1989-08-31 | 2002-12-23 | 13 |
| Chispa | 1998-09-11 | 2002-12-23 | 4 |
| Wicho | 2000-02-09 | 2002-12-23 | 2 |
| Skim | 2001-04-29 | 2002-12-23 | 1 |
| Pelusa | 2000-03-30 | 2002-12-23 | 2 |
+--------+------------+--------------+------+
9 rows in set (0.01 sec)Aquí, YEAR( ) obtiene únicamente el año y RIGHT( ) obtiene los cinco caracteres más a la derecha de cada una de las fechas, que representan el mes y el día (MM-DD). La parte de la expresión que compara los valores MM-DD se evalúa a 1 o 0, y permite ajustar el valor de la edad en el caso de que el valor MM-DD de la fecha actual ocurra antes del valor MM-DD de la fecha de nacimiento.
Dado que la expresión en sí es bastante fea, se ha usado un alias (edad) que es el que aparece como etiqueta en la columna que muestra el resultado de la consulta.
Esta consulta debe trabajar bien, pero el resultado puede ser de alguna manera más útil si las filas son presentadas en algún orden. Para ello haremos uso de la cláusula ORDER BY.
Por ejemplo, para ordenar por nombre, usaremos la siguiente consulta:
mysql> SELECT nombre, nacimiento, CURRENT_DATE,
-> (YEAR(CURRENT_DATE) - YEAR(nacimiento))
-> - (RIGHT(CURRENT_DATE,5) < RIGHT(nacimiento,5))
-> AS edad FROM mascotas ORDER BY nombre;
+--------+------------+--------------+------+
| nombre | nacimiento | CURRENT_DATE | edad |
+--------+------------+--------------+------+
| Buffy | 1999-05-13 | 2002-12-23 | 3 |
| Chispa | 1998-09-11 | 2002-12-23 | 4 |
| FanFan | 2000-08-27 | 2002-12-23 | 2 |
| Fluffy | 1999-02-04 | 2002-12-23 | 3 |
| Kaiser | 1989-08-31 | 2002-12-23 | 13 |
| Mau | 1998-03-17 | 2002-12-23 | 4 |
| Pelusa | 2000-03-30 | 2002-12-23 | 2 |
| Skim | 2001-04-29 | 2002-12-23 | 1 |
| Wicho | 2000-02-09 | 2002-12-23 | 2 |
+--------+------------+--------------+------+
9 rows in set (0.00 sec)Para ordenar por edad en lugar de nombre, únicamente tenemos que usar una cláusula ORDER BY diferente:
mysql> SELECT nombre, nacimiento, CURRENT_DATE,
-> (YEAR(CURRENT_DATE) - YEAR(nacimiento))
-> - (RIGHT(CURRENT_DATE,5) < RIGHT(nacimiento,5))
-> AS edad FROM mascotas ORDER BY edad;
+--------+------------+--------------+------+
| nombre | nacimiento | CURRENT_DATE | edad |
+--------+------------+--------------+------+
| Skim | 2001-04-29 | 2002-12-23 | 1 |
| FanFan | 2000-08-27 | 2002-12-23 | 2 |
| Wicho | 2000-02-09 | 2002-12-23 | 2 |
| Pelusa | 2000-03-30 | 2002-12-23 | 2 |
| Fluffy | 1999-02-04 | 2002-12-23 | 3 |
| Buffy | 1999-05-13 | 2002-12-23 | 3 |
| Mau | 1998-03-17 | 2002-12-23 | 4 |
| Chispa | 1998-09-11 | 2002-12-23 | 4 |
| Kaiser | 1989-08-31 | 2002-12-23 | 13 |
+--------+------------+--------------+------+
9 rows in set (0.01 sec)Una consulta similar puede ser usada para determinar la edad que tenía una mascota cuando falleció. Para determinar que animalitos ya fallecieron, la condición es que el valor en el campo fallecimiento no sea nulo (NULL). Entonces, para los registros con valor no-nulo, calculamos la diferencia entre los valores fallecimiento y nacimiento.
mysql> SELECT nombre, nacimiento, fallecimiento,
-> (YEAR(fallecimiento) - YEAR(nacimiento))
-> - (RIGHT(fallecimiento,5) < RIGHT(nacimiento,5))
-> AS edad FROM mascotas WHERE fallecimiento IS NOT NULL;
+--------+------------+---------------+------+
| nombre | nacimiento | fallecimiento | edad |
+--------+------------+---------------+------+
| Kaiser | 1989-08-31 | 1997-07-29 | 7 |
+--------+------------+---------------+------+
1 row in set (0.01 sec)La consulta usa fallecimiento IS NOT NULL, en vez de falleciimiento < > NULL porque NULL es una valor especial. Esto será explicando más a detalle posteriormente.
¿Qué sucede si deseamos conocer cuáles de nuestras mascotas cumplen años el próximo mes? Para este tipo de cálculos, el año y el día son irrelevantes; simplemente tenemos que extraer el valor del mes en la columna nacimiento. Como se mencionó anteriormente, MySQL proporciona diversas funciones para trabajar y manipular fechas, en este caso haremos uso de la función MONTH( ). Para ver como trabaja, vamos a ejecutar una consulta muy simple que muestra tanto el valor de una fecha como el valor que regresa la función MONTH( ).
mysql> SELECT nombre, nacimiento, MONTH(nacimiento) FROM mascotas;
+--------+------------+-------------------+
| nombre | nacimiento | MONTH(nacimiento) |
+--------+------------+-------------------+
| Fluffy | 1999-02-04 | 2 |
| Mau | 1998-03-17 | 3 |
| Buffy | 1999-05-13 | 5 |
| FanFan | 2000-08-27 | 8 |
| Kaiser | 1989-08-31 | 8 |
| Chispa | 1998-09-11 | 9 |
| Wicho | 2000-02-09 | 2 |
| Skim | 2001-04-29 | 4 |
| Pelusa | 2000-03-30 | 3 |
+--------+------------+-------------------+
9 rows in set (0.00 sec)Encontrar los animalitos cuyo cumpleaños es el próximo mes es muy sencillo. Suponiendo que el mes actual es Abril (valor 4), entonces tenemos que buscar los registros cuyo valor de mes sea 5 (Mayo).
mysql> SELECT nombre, nacimiento FROM mascotas WHERE MONTH(nacimiento) = 5;
+--------+------------+
| nombre | nacimiento |
+--------+------------+
| Buffy | 1999-05-13 |
+--------+------------+
1 row in set (0.00 sec)Aquí habrá por supuesto una complicación si el mes actual es Diciembre. No podemos simplemente agregar uno al número del mes (12) y buscar los registros cuyo mes de nacimiento sea 13 porque dicho mes no existe. En vez de esto, tenemos que buscar los animalitos que nacieron en Enero (mes 1).
Sin embargo, lo mejor es que podemos escribir una consulta que funcione no importando cuál sea el mes actual. La función DATE_ADD( ) nos permite agregar un intervalo de tiempo a una fecha dada. Si agregamos un mes al valor regresado por la función NOW( ), y entonces extraemos el valor del mes con la función MONTH( ), el resultado es que siempre obtendremos el mes siguiente.
La consulta que resuelve nuestro problema queda así:
mysql> SELECT nombre, nacimiento FROM mascotas
-> WHERE MONTH(nacimiento) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH)); |