Trabajar con fechas en MySQL

Al utilizar fechas en MySQL, debe recordar que siempre debe incluir primero el año. Para escribir 18 de julio de 2008 deber utilizar ‘2008-07-18’. No utilice ’07-18-2008′ o ’18-07-2008′ como está acostumbrado.
Con las fechas puede realizar diferentes operaciones:

  • Ordenar por fecha
  • Buscar fechas o intervalos de fecha concretos.
  • Extraer partes de un valor de fecha, como el año, mes o día.
  • Calcular la diferencia entre fechas.
  • Calcular una fecha sumando o restando un intervalo

A continuación veremos diversos ejemplos.
Para buscar una fecha concreta, ya sea por el valor exacto o en relación a otro valor, compare una columna DATE con el valor deseado:

mysql> SELECT * FROM grade_event WHERE date= '2008-10-01';

date category event_id
2008-10-01 T 6

mysql> SELECT last_name, first_name, death
-> FROM president
-> WHERE death >= '1970-01-01' AND death < '1980-01-01';
last_name first_name death
Truman Harry S 1972-12-26
Johnson Lyndon B. 1973-01-22

Para probar o recuperar partes de fechas, utilice funciones como YEAR(), MONTH() o DAYOFMONTH(). Por ejemplo, para buscar presidentes nacidos en marzo, busque fechas que tengan el valor 3 para el mes:

mysql> SELECT last_name, first_name, birth
-> FROM president WHERE MONTH(birth) = 3;

last_name first_name birth
Madison James 1751-03-16
Jackson Andrew 1767-03-15
Tyler John 1790-03-29
Cleveland Grover 1837-03-18

La consulta también se podría escribir con el nombre del mes:

mysql> SELECT last_name, first_name, birth
-> FROM president WHERE MONTHNAME(birth) = 'March';

last_name first_name birth
Madison James 1751-03-16
Jackson Andrew 1767-03-15
Tyler John 1790-03-29
Cleveland Grover 1837-03-18

Para ser más concretos, puede combinar MONTH() y DAYOFMONTH() para buscar presidentes nacidos un día concreto del mes de marzo:

mysql> SELECT last_name, first_name, birth
-> FROM president WHERE MONTH(birth) = 3 AND DAYOFMONTH(birth) = 29;

last_name first_name birth
Tyler John 1790-03-29

Es el tipo de consulta que utilizaría para generar una lista de cumpleaños de famosos que aparecen en los periódicos. No obstante, para seleccionar filas que coincidan con el mes y el día de la fecha actual, no tiene que añadir valores literales como en el ejemplo anterior. Para buscar presidentes nacidos hoy, independientemente del día que sea, basta con comparar su fecha de nacimiento con la parte del mes y el día con CURDATE(), que siempre devuelve la fecha actual:

SELECT last_name, first_name, birth FROM president WHERE MONTH(birth) = MONTH(CURDATE()) AND DAYOFMONTH(birth) = DAYOFMONTH(CURDATE());

Puede restar una fecha de otra, lo que le permite buscar el intervalo entre fechas. Por ejemplo, para determinar qué presidentes vivieron más, reste la fecha de defunción de la de nacimiento. La función TIMESTAMPDIFF() acepta un argumento para especificar la unidad en la que expresar el resultado (en este caso, YEAR):

mysql> SELECT last_name, first_name, birth, death,
-> TIMESTAMPDIFF(YEAR, birth, death) AS age
-> FROM president WHERE death IS NOT NULL
-> ORDER BY age DESC LIMIT 5;

last_name first_name birth death age
Reagan Ronald W. 1911-02-06 2004-06-05 93
Ford Gerald R. 1913-07-14 2006-12-26 93
Adams John 1735-10-30 1826-07-04 90
Hoover Herbert C. 1874-08-10 1964-10-20 90
Truman Harry S 1884-05-08 1972-12-26 88

Otra función que tenemos para calcular una diferencia entre fechas medida en días, consiste en usar la función TO DAYS(), que convierte una fecha en días. Una aplicación de esta función es calcular la distancia de una fecha con respecto a otra. Por ejemplo, puede saber qué miembros de la asociación Historical League deber renovar su suscripción. Calcule la diferencia entre la fecha de vencimiento y la actual, y si es menor aun valor de umbral, necesitán renovar. La siguiente consulta suscripciones vencidas o que tendrán que renovarse en 60 días:

SELECT last_name, first_name, expiration FROM member WHERE (TO_DAYS(expiration) - TO_DAYS(CURSATE())) < 60;

La instrucción equivalente con TIMESTAMPDIFF() sería la siguiente:

SELECT last_name, first_name, expriation FROM member WHERE TIMESTAMPDIFF(DAY, CURDATE(), expiration) < 60;

Para calcular una fecha de otra, puede usar DATE_ADD() o DATE_SUB(). Estas funciones aceptan una fecha y un intervalo para crear una nueva fecha. Por ejemplo:

mysql> SELECT DATE_ADD('1970-1-1', INTERVAL 10 YEAR);
DATE_ADD('1970-1-1', INTERVAL 10 YEAR
1980-01-01

mysql> SELECT DATE_SUB('1970-1-1', INETRVAL 10 YEAR);
DATE_SUB('1970-1-1', INTERVAL 10 YEAR
1980-01-01

En una consulta anterior seleccionamos presidentes que murieron en los años 70, con fechas literales para finalizar el intervalo de selección. Podríamos volver a escribir esa consulta y utilizar una fecha incial literal y una fecha final calculada a partir de la fecha incial y un intervalor:

mysql> SELECT last_name, first_name, death
-> FROM president
-> WHERE death >= '1970-1-1'
-> AND death < DATE_ADD('1970-1-1', INTERVAL 10 YEAR);

last_name first_name death
Truman Harry S 1972-12-26
Johnson Lyndon B. 1973-01-22

La consulta de renovación de suscripciones también se podría modificar mediante la utilización de DATE_ADD();

SELECT last_name, first_name, expiration FROM member WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 60 DAY);

Si la columna expiration está indexada, resultará más eficaz.

Comparte