Trabajar con fechas en MySQL – felipe.lavin.blog

Trabajar con fechas es una tarea que al estar sujeta a tantas reglas especiales (meses con distinta cantidad de días, años bisiestos, comienzo de mes a media semana, etc) puede ser muy compleja si lo intentamos abordar sin el apoyo de una buena base.

Anteriormente he escrito sobre cómo trabajar con fechas en PHP y en Javascript: en ambos casos contamos con el concepto de fechas como objetos, lo que ordena y facilita bastante su manipulación a través de los métodos propios de cada lenguaje.

En MySQL existe a su vez un concepto de fechas y tiempos como tipos de datos con sus propias características, y junto con ellos, un montón de funciones que nos pueden servir para realizar distintos cálculos y operaciones al realizar consultas.

Tipos de fecha y horas

En MySQL existen los siguientes tipos de fecha y tiempo:

DATETIME corresponde a una representación completa de fecha y hora, es decir, algo como el 25 de octubre del 2014 a las 8:00 pm, lo que se almacena del modo 2014-10-25 20:00:00. Es notable la ausencia de la indicación de una zona horaria, cuya gestión queda delegada a la aplicación.
Los tipos DATE y TIME, como quizás ya estás adivinando, almacenan solamente fecha o solamente hora. Los formatos son los mismos que su parte correspondiente en una columna DATETIME, es decir, YYYY-MM-DD para las fechas y HH:MM:SS para el tiempo.
El tipo YEAR es un dato aun más específico y almacena solamente un año. Aunque se puede almacenar sólo con 2 dígitos, creo que no es necesario ahondar en lo evidentemente necesario de utilizar el formato de 4 dígitos.
Finalmente, existe el tipo TIMESTAMP que es similar a DATETIME pero con la salvedad de que todos los datos son convertidos hacia y desde UTC al escribir y leer la información, tomando como base la configuración de zona horaria del servidor, o bien, si se ha especificado, de la conexión con el motor de base de datos. Por ejemplo, si mi servidor está en la zona horaria de Santiago de Chile y voy a guardar el dato 2014-01-30 12:00:00, MySQL lo convierte a 2014-01-30 15:00:00 que sería la fecha y hora correspondientes en UTC.

Utilizando fechas y horas en consultas

Como ya sabemos que MySQL “entiende” de fechas y horas, podemos hacer consultas relacionadas con éstas de forma muy sencilla. Por ejemplo, si queremos averiguar las entradas de WordPress publicadas en una jornada de trabajo en particular:

SELECT * FROM wp_posts
WHERE post_date >= '2014-03-19 08:00:00'
AND post_date <= '2014-03-19 18:00:00'
AND post_status = 'publish'
ORDER BY post_date ASC

… donde post_date es una columna con datos tipo DATETIME.

También podríamos consultar por todas las entradas publicadas un día en particular, sin importar la hora. Para esto, una opción ingenua sería:

SELECT * FROM wp_posts
WHERE post_date >= '2014-03-19 00:00:00'
AND post_date <= '2014-03-19 23:59:59'
AND post_status = 'publish'
ORDER BY post_date ASC

Pero también podemos tratar esa columna como si tuviera solamente información de fecha utilizando la función CAST de MySQL:

SELECT * FROM wp_posts
WHERE CAST(post_date AS DATE) = '2014-03-19'
AND post_status = 'publish'
ORDER BY post_date ASC

Además podemos extraer parte de la fecha, por ejemplo:

SELECT  * FROM wp_posts
WHERE YEAR( post_date ) = '2014'
AND MONTH( post_date ) = '03'
AND post_status = 'publish'
ORDER BY post_date ASC

Utilizar periodos de tiempo en consultas

También podemos hacer consultas considerando periodos de tiempo; lo que es especialmente útil cuando queremos saber, por ejemplo, las entradas publicadas en un mes en particular. En lugar de tratar de adivinar cuántos días tiene el mes por el que estamos consultando, podemos definir un rango de forma más sencilla; por ejemplo, podemos formular la misma consulta anterior de este modo:

SELECT * FROM wp_posts WHERE
CAST(post_date AS DATE) >= '2014-03-01'
AND post_date < DATE_ADD( '2014-03-01', INTERVAL 1 MONTH )
AND post_status = 'publish'
ORDER BY post_date ASC