¡Hola! Me parece que andas usando un bloqueador de anuncios =( ¿Nos desbloqueas? ¿Por qué?
F13

Funcion 13

Funciones útiles de MySQL (Primera parte) - Fecha y Hora

MySQL es sin duda uno de los motores SQL más conocidos y más usados en el ámbito web. No quiere decir que sea el mejor ni el peor pero sin duda es la opción más común en cualquier servicio de hosting.

A menudo olvidamos (o desconocemos) que MySQL contiene muchas funciones muy pero que muy útiles y que te pueden hacer la vida mucho más sencilla. Recientemente migramos un sistema en mi empresa y unos cálculos que antes los realizaba un script, los hemos delegado directamente a MySQL.

En este artículo no vamos a hablar sobre el rendimiento de estas funciones ya que, sinceramente, desconozco estos pormenores. Si hay alguien que sepa sobre el tema y quiera compartir con nosotros su sabiduría, ¡bienvenido sea!

Funciones de fecha, hora y datetime de MySQL

Comenzaré con estas funciones porque son las que tengo más recientes en mi trabajo reciente y creo que son de las más útiles. Las fechas, para mi gusto, es uno de los problemas más pesados de todos los lenguajes de programación.

Dado que en estoy acostumbrado a trabajar con llamadas, no voy a complicarme mucho la existencia y usaré un ejemplo de varias llamadas. Como puedes ver un poco más abajo, la mayoría de los campos son fechas con el formato YYYY-MM-DD (HH:M:SS).

+---------+---------+---------------------+---------------------+---------------------+
| origen  | destino | fecha               | respondida          | cuelgue             |
+---------+---------+---------------------+---------------------+---------------------+
| Piccolo | Vegeta  | 2012-09-26 00:00:00 | 2012-09-26 00:01:00 | 2012-09-26 00:04:50 |
| Goku    | Yamcha  | 2012-09-26 06:00:00 | 2012-09-26 06:02:50 | 2012-09-26 06:54:51 |
| Gohan   | Trunk   | 2012-09-26 10:43:21 | 2012-09-26 10:44:21 | 2012-09-26 11:24:59 |
+---------+---------+---------------------+---------------------+---------------------+

¡Esas fechas son horribles! Para estos casos, la función DATE_FORMAT() nos viene como anillo al dedo, ya que funciona de forma muy similar a la función date de PHP. Lo que pasa, es que con PHP tendríamos que hacer uso de la función strtotime para convertirla primero a formato Unix.

Para darle formato a la fecha con la función DATE_FORMAT, tendremos que hacer uso del caracter '%'.

Veamos cómo podríamos quién hizo la llamada y cuando, pero con un formato más bonito. Pero antes de que podamos apreciar lo que va a solucionarnos la vida, veamos cómo lo haríamos en PHP.

La consulta sería

SELECT origen, fechaLlamada FROM llamadas;  

Y esto es como lo haríamos en PHP:

while ($llamada = mysqli_fetch_assoc($llamadas)) {  
    $fecha_unix = strtotime($llamada['fechaLlamada']);
    $fecha_formateada = date('d/m/Y a las H:i', $fecha_unix);
    echo '<p>' . $llamada['origen'] . ' : ' . $fecha_formateada . '</p>' . PHP_EOL;
}

Sin embargo, ajustando mínimamente nuestra consulta:

SELECT origen, DATE_FORMAT(fechaLlamada,'%d/%m/%Y a las %H:%i') AS fechaLlamada FROM llamadas  

Estas son las filas devueltas:

+---------+------------------------+
| origen  | fechaLlamada           |
+---------+------------------------+
| Piccolo | 26/09/2012 a las 00:00 |
| Goku    | 26/09/2012 a las 06:00 |
| Gohan   | 26/09/2012 a las 10:43 |
+---------+------------------------+

Nuestro código PHP se verá reducido:

while ($llamada = mysqli_fetch_assoc($llamadas)) {  
    echo '<p>' . $llamada['origen'] . ' : ' . $llamada['fechaLlamada'] . '</p>' . PHP_EOL;
}

¿Fácil verdad? Puede que no te parezca demasiado pero, créeme, cuando comienzas a manipular fechas en tu aplicación, la diferencia puede ser muy notable.

Obteniendo diferencias entre fechas

Estas funciones son las que, sin duda, me han salvado el pellejo más de una vez. Especialmente a la hora de hacer algo rápido, ver algo que sea destacable, etc. Veamos cómo podemos obtener la duración de las llamadas. Para ello nos vamos a servir de la función TIMESTAMPDIF, ya que estamos comparando campos del tipo TIMESTAMP. Por supuesto, existen las funciones DATEDIFF y TIMEDIFF para los campos DATE y los campos del tipo TIME respectivamente.

La consulta sería algo así:

SELECT origen, destino, TIMESTAMPDIFF(SECOND, fechaRespondida, fechaCuelgue) AS duracion FROM llamadas;  

Y esto nos devolvería la diferencia entre ambas fechas, en este caso en segundos. Se pueden usar parámetros como MINUTE, DAY, WEEK, etc. No obstante, para este tipo de datos, con poca diferencia, nos viene de perlas. Este es el resultado:

+---------+---------+----------+
| origen  | destino | duracion |
+---------+---------+----------+
| Piccolo | Vegeta  |      230 |
| Goku    | Yamcha  |     3121 |
| Gohan   | Trunk   |     2438 |
+---------+---------+----------+

Obteniendo la fecha/hora actual

Si necesitas usar la fecha, la hora o ambos a la vez en alguna de tus consultas, no necesitas que PHP haga nada por ti. Usa una de estas funciones:

  • NOW() nos devuelve la fecha actual en el formato YYYY-MM-DD (HH:M:SS).
  • CURDATE() nos devuelve la fecha actual en el formato YYYY-MM-DD.
  • CURTIME() nos devuelve la hora actual en el formato HH:M:SS.

Estas funciones son muy útiles por ejemplo para establecer la fecha de actualización de una fila. Por ejemplo,

UPDATE llamadas SET revisada = NOW() WHERE origen = 'Piccolo';  

Vamos a mezclar un poco la función que vimos antes con esta para saber cuándo fue la última vez que llamó cada personaje.

SELECT a.origen,  
       Timestampdiff(day, a.fechallamada, Now()) AS diasDesdeUltimaLlamada 
FROM   llamadas a  
       INNER JOIN (
       SELECT id, 
         Max(fechallamada) AS ultimaLlamada 
         FROM  llamadas 
         GROUP BY origen) b 
         ON ( 
          b.id = a.id AND 
          b.ultimallamada = a.fechallamada 
         )
       )

Esta consulta es un poco más compleja pero puede resultar de utilidad. Si tenéis duda con los JOINS, echadle un ojo a un artículo que publicamos con una explicación visual sobre los JOINs SQL.

+---------+------------------------+
| origen  | diasDesdeUltimaLlamada |
+---------+------------------------+
| Gohan   |                      2 |
| Goku    |                      2 |
| Piccolo |                      2 |
+---------+------------------------+

Añadiendo y restando fechas y horas

Las funciones DATE_ADD() y DATE_SUB() nos ofrecen una forma muy sencilla de añadir o restar períodos de una fecha. Como parámetros reciben una fecha y un intervalo. El intervalo es una representación de un período de tiempo como 1 DAY, 50 YEAR, etc.

Yo las uso a menudo para extraer registros del día anterior al que estoy ejecutando la consulta, para temas de informes. Veamos cómo podemos usarlo:

SELECT * FROM llamadas WHERE fechaLlamada = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY));  

Fácil, sencillo y para toda la familia.

Conclusión

Puede que no te hayan parecido demasiado interesantes pero a mi, las funciones de MySQL me han hecho la vida mucho más fácil.

Si el artículo te pareció interesante, útil o incluso equivocado, por favor considera el dejar un comentario. ¡Lo apreciaré mucho!

Programador Front-end en First + Third y Potato. Trabajando con JavaScript y HTML5 desde el corazón de Sevilla.

Comentarios ¡Únete a la conversación!