Manejando tiempo con PHP y MySQL

En este tutorial voy a tratar el tema del tiempo en PHP y MySQL. Vamos a aprender a utilizar el instante UNIX y el tipo de campo de MySQL TIMESTAMP, así como varias funciones útiles para usar con ambos dos. La última parte del tutorial incluye el código fuente completo de un sistema de búsqueda de registros por tiempo en una base de datos MySQL. Espero que os aclare ciertas dudas que tengáis. Sin más preámbulos, vamos a empezar. Dependiendo del origen de cada uno, la forma de escribir la fecha varía bastante. En España es habitual utilizar el formato día-mes-año, pero los anglosajones utilizan el formato mes-dia-año. Así pues, si queremos poder trabajar con fechas de todas clases, esa es una de las cosas que debemos tener en cuenta.       En lo que a MySQL se refiere, éste posee varios campos para almacenar datos de tipo horario, pero quizás el más usado es TIMESTAMP. Éste es un campo con ciertas peculiaridades que vamos a examinar más detenidamente. Lo primero, es la longitud del campo. En un campo de tipo VARCHAR, por ejemplo, una longitud de 200 indica que éste admitirá hasta 200 caracteres. En el caso del tipo TIMESTAMP, ésto es un poco diferente. Éste campo almacena los datos con el siguiente formato:

YYYYMMDDHHIISS, si posee una longitud de 14
YYYYMMDDHHII, si posee una longitud de 12
YYYYMMDDHH, si posee una longitud de 10
YYYYMMDD, si posee una longitud de 8
YYYYMM, si posee una longitud de 6
YYYY, si posee una longitud de 4

Esto, visto así, según aparece en la documentación del manual de MySQL, puede sonar a chino, pero no lo es. Hay que dividir el campo en dos partes: La destinada a la fecha y la destinada a la hora. El campo significa Y para año, M para mes, D para día, H para hora, I para minuto y S para segundo. Así pues, el 17 de diciembre del 2002, a las 17:45 horas y 23 segundos se escribe así en un TIMESTAMP de 14 de longitud: 20021217174523 Indico lo de las dos cifras a propósito, porque una fecha como 1 de Enero del 2002 a 1:05 y 4 segundos de la madrugada se escribe así: 20020101010504       A mi, sin embargo, el campo TIMESTAMP me parece un poco engorroso. Es complicado trabajar con él una vez has recogido el dato en una variable de PHP, pues has de tratarlo como una cadena, cortando aquí y allá para poder agregar minutos o días. Además, has de tratar esas partes como enteros en vez de como cadenas. Por eso, utilizo el formato de fecha UNIX (segundos transcurridos desde el 1-1-1970). He creado dos tablas en una base de datos, en los ficheros del tutorial. Os recomiendo que las uséis ambas, para, de ese modo comparar una y otra y ver con qué formato os gusta trabajar más. En el tutorial explicaré los ejemplos para ambos, por supuesto :-)

MySQL, además de tener compatibilidad con el estándar ANSI, posee un gran número de funciones que pueden ser usadas dentro de las sentencias SQL. Las hay de varios tipos, matemáticas, de cadenas... y de tiempo. Estas últimas son las que nos interesan. Para entenderlas haré varios ejemplos con cada una. Copia-pega y ejecuta las sentencias SQL dentro del monitor. Comenzaremos por una sencilla sentencia de inserción de un registro en la tabla de nuestra base de datos.

INSERT INTO ARTICULOS_UNIXTIME (id_articulo,nombre,precio,momento_agregado) VALUES ('','Teclado','6',UNIX_TIMESTAMP());
INSERT INTO ARTICULOS_TIMESTAMP (id_articulo,nombre,precio,momento_agregado) VALUES ('','Teclado','6',NOW());

Aquí, pues, vemos dos funciones. La primera es UNIX_TIMESTAMP(). Lo que hace es insertar dentro de la base de datos el instante UNIX actual en el campo especificado. La función NOW(), inserta en el campo especificado el momento actual expresado en instante TIMESTAMP. Como antes dije, es cuestión de preferencia el usar un tipo de dato u otro, pero si nos decantamos por el instante UNIX, hay una función que debemos conocer, porque es, (como se dice en España), LA MADRE DEL CORDERO (esto es, el quid de la cuestión). Esta función es FROM_UNIXTIME() Para entender su funcionamiento, usémosla en un ejemplo:

SELECT FROM_UNIXTIME(momento_agregado) FROM ARTICULOS

Como véis, esta sentencia devuelve la fecha formateada del siguiente modo: YYYY-MM-DD HH:mm:ss ¿Se puede formatear la fecha a través de esta función? La respuesta es si. A continuación incluyo una tabla en la que se explican los argumentos más comunes de la función.

ARGUMENTO    QUE INDICA
%M          Nombre del més en inglés.
%W          Nombre del día de la semana en inglés
%D          Día del mes con sufijo inglés (1st, 2nd, 3rd...) (primero, segundo, tercero...)
%Y          Año con cuatro dígitos
%y          Año con dos dígitos
%d          Día del mes, numérico (00....31)
%e          Día del mes, numérico (0....31)
%m          Mes, numérico (01...12)
%c          Mes, numérico (1...12)
%j          Día del año (001...366)
%H          Hora (00....23)
%k          Hora (0...23)
%h          Hora (01...12)
%i          Minutos en formato numérico (00...59)
%S          Segundos (00...59)
%p          AM ó PM
%w          Día de la semana (0 es Domingo y 6 es Sábado)

Para formatear un campo de tipo TIMESTAMP, usaremos la función DATE_FORMAT(), que utiliza los mismos argumentos que FROM_UNIXTIME(). Para una lista completa de argumentos, échale un vistazo a la documentación de MySQL [#TODO VINCULO]. Al igual que un instante UNIX puede ser transformado en una cadena formateada, un campo TIMESTAMP puede ser expresado como instante UNIX a través de la función UNIX_TIMESTAMP() y un argumento. Compruébalo ejecutando estas sentencias:

SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); 
SELECT UNIX_TIMESTAMP('19971004222300');

Además de formatear la fecha, tenemos a nuestra disposición un gran arsenal de funciones que pueden simplificarnos el trabajo enormemente. Vamos a ver algunas de ellas a través de ejempos prácticos. Ejemplo: Queremos los registros de una base de datos comprendidos entre Abril del 2002 y Julio del 2002.

SELECT * FROM ARTICULOS_UNIXTIME WHERE MONTH(FROM_UNIXTIME(momento_agregado)) IN (11) AND YEAR(FROM_UNIXTIME(momento_agregado)) IN (2009)
SELECT * FROM ARTICULOS_TIMESTAMP WHERE MONTH(momento_agregado) IN (11) AND YEAR(momento_agregado) IN (2009)

Como vemos, la diferencia entre utilizar un campo TIMESTAMP y un instante UNIX es que, antes de aplicar sobre un campo instante UNIX una de las funciones de tiempo de MySQL, éste debe ser previamente convertido al formato TIMESTAMP internamente. Quizás te estés preguntando que si las sentencias son más largas y hay que utilizar FROM_UNIXTIME(), ¿qué ventajas tiene este campo?. Para empezar, en un sistema operativo UNIX/Linux, todo el tiempo se mide de este modo (en instantes UNIX), así es que por ese lado puede ser útil. De todos modos ya dije que es elección personal de cada uno. Hemos visto dos funciones nuevas: MONTH() Y YEAR(). Dado que estábamos usándolas como criterio de comparación, no podemos ver lo que devuelven, pero ejecutad las siguientes sentencias para ver cual es el resultado devuelto por ambas funciones:

SELECT MONTH(FROM_UNIXTIME(momento_agregado)) FROM ARTICULOS_UNIXTIME;
SELECT YEAR(FROM_UNIXTIME(momento_agregado)) FROM ARTICULOS_UNIXTIME;
SELECT MONTH(momento_agregado) FROM ARTICULOS_TIMESTAMP;
SELECT YEAR(momento_agregado) FROM ARTICULOS_TIMESTAMP;

Otro ejemplo: Queremos los registros correspondientes a los días 1, 2 y 5 de cada mes, solo en los años 2000,2001 y 2002

SELECT * FROM ARTICULOS WHERE DAYOFMONTH(FROM_UNIXTIME(momento_agregado)) IN (1,2,5) AND YEAR(FROM_UNIXTIME(momento_agregado)) IN (2000,2001,2002);     
SELECT * FROM ARTICULOS WHERE DAYOFMONTH(momento_agregado) IN (1,2,5) AND YEAR(momento_agregado) IN (2000,2001,2002);

Aquí vemos la función DAYOFMONTH(), que devuelve el día del mes de 1 a 31 dada una fecha. Y esto es todo lo que voy a explicar en cuanto a funciones de MySQL se refiere. Para mayor información, visitad la referencia de MySQL en esta página. Hay muchas otras funciones, muchas de ellas muy específicas y que en realidad no vamos a usar nunca. Eso sí, la documentación está en inglés. Desconozco si hay alguna traducción del manual entero de MySQL, pero creo que no. De todos modos, podéis echa un buen vistazo a la página y empaparos de este tema (yo lo hice para poder escribir este tutorial)