En este Curso MySQL hablaremos de los tipos de datos que nos permiten representar valores de tiempo. Los campos de datos para fecha y hora son de uso habitual y MySQL nos ofrece los siguientes tipos: DATE, TIME, DATETIME, TIMESTAMP y YEAR. Cada uno de estos tipos de datos cuentan con un rango de valores aceptados, así como el valor “cero” que es establecido automáticamente cuando se especifica un valor erróneo que MySQL no puede representar.
Es necesario conocer las siguientes consideraciones generales cuando se trabaja tipos de datos de tiempo:
- MySQL obtiene los valores para una fecha u hora dado en un formato estándar de salida, por el contrario al momento de introducir valores intenta con una gran variedad de formatos en pos de interpretar correctamente los datos insertados.
- A pesar de que MySQL intenta interpretar los valores en varios formatos, las fechas siempre deben declararse en el orden año-mes-día (por ejemplo: 2015-04-23 o 98-08-15).
- Las fechas que contienen dos dígitos en el segmento del año, como en el ejemplo anterior, generan ambigüedad ya que no conocemos el siglo al que pertenece. Por esta razón MySQL se guía por la siguiente regla a la hora de interpretar este formato:
- Años especificados en los rangos 70-99 son convertidos a 1970-1999.
- Años especificados en los rangos 00-69 son convertidos a 2000-2069.
- En el punto anterior y específicamente con la regla que usa MySQL para interpretar los años especificados con dos dígitos, nos surge una duda. ¿Cómo representar con dos dígitos fechas anteriores a 1970 y posteriores a 2069? Con respecto a esto no he encontrado referencias por lo que asumo que habría que especificar el años en su valor numérico completo.
- Hasta cierto punto uno puede convertir un valor de tiempo en otro. No obstante puede que en ocasiones ocurran alteraciones en los datos y pérdida de información en estas operaciones. En todos los casos la conversión entre valores de fecha o tiempo a otros tipos de datos similares está sujeto a los rangos de valores aceptados por el tipo de dato final. Por ejemplo, aunque los valores de los tipos de datos DATE, DATETIME y TIMESTAMP se pueden especificar usando el mismo formato, estos tipos de datos no comparten los mismos rangos de valores. TIMESTAMP no acepta valores anteriores a 1970 UTC o posteriores a “2038-01-19 03:14:07” UTC. Esto significa que fechas como “1968-01-01”, siendo válidas para los tipos de datos como DATE y DATETIME, no lo son con TIMESTAMP y son convertidas a “0″ por MySQL.
- MySQL permite fechas donde día o mes y día sean cero en columnas de tipo DATE o DATETIME. Esto puede ser útil en casos donde se desea almacenar fechas que pudiéramos no conocer del todo. En estos caso serían válidos los siguientes valores “2015-00-00” o “2015-01-00”. Si se decide manejar fechas en este formato no podemos esperar resultaos correctos de funciones como DATE_SUB() o DATE_ADD() que requieren de fechas completas. Para prohibir el uso de esta característica debemos de habilitar el modo Strict SQL a partir de la versión MySQL 5.7.4 o el modo NO_ZERO_IN_DATE para versiones anteriores a esta.
Descripción de los tipos de datos para fecha y hora:
Importante recordar que donde quiera que veamos [ … ] esto quiere decir que todo cuanto engloba es un parámetro opcional, en el caso de < … > es todo lo contrario, es un parámetro obligatorio y que de omitirlo seguramente obtendremos un error de sintaxis.
DATE
Permite declarar fechas en los rangos de “1000-01-01” a “9999-12-31”. MySQL muestra los valores de tipo DATE en el formato “YYYY-MM-DD” y permite la asignación usando tanto cadenas de texto como números.
Ejemplo:
DATETIME [ ( fsp ) ]
Permite declarar fecha y hora. Rangos válidos de “1000-01-01 00:00:00.000000” a “9999-12-31 23:59:59.999999”. MySQL muestra los valores de tipo DATETIME en el formato “YYYY-MM-DD HH:MM:SS[fracción]” y permite la asignación usando tanto cadenas de texto como números.
El valor opcional fsp en los rangos de 0 a 6 puede ser establecido en pos de especificar la precisión fraccionaria de los segundos. Un valor de cero significa que no hay parte fraccionaria. Si este valor es omitido la precisión por defecto es de cero.
La inicialización y actualización automática de columnas de tipo DATETIME con la fecha y hora actual puede especificarse usando las cláusulas DEFAULT y ON UPDATE.
Ejemplo:
|
|
record_mundial DATETIME(4)
|
En este ejemplo establecemos en nuestra tabla imaginaria de record mundiales una columna con la fecha y hora exacta de este suceso, especificando una precisión de 4 dígitos para los milisegundos, ya que 1 segundo = 1000 milisegundos, de esta manera podremos distinguir con más exactitud cual de nuestros deportistas realmente ha ganado el título en caso de tener resultados finales muy aproximados.
TIMESTAMP [ ( fsp ) ]
Permite declarar un momento en el tiempo, una marca de tiempo que almacena los valores como la cantidad de segundos desde Epoch (“1970-01-01 00:00:00” UTC). Un campo TIMESTAMP no puede representar el valor “1970-01-01 00:00:00” debido a que sería equivalente a cero segundos desde Epoch y el valor cero está reservado por MySQL.
El valor opcional fsp en los rangos de 0 a 6 puede ser establecido en pos de especificar la precisión fraccionaria de los segundos. Un valor de cero significa que no hay parte fraccionaria. Si este valor es omitido la precisión por defecto es de cero.
A no ser de que se especifique lo contrario, la primera columna de tipo TIMESTAMP por defecto se establece el valor de fecha y hora de la última vez que fue actualizada la tabla donde se encuentra esta columna y siempre que no se le asigne explícitamente un valor. Esto hace al tipo de dato TIMESTAMP muy útil cuando se quiere conocer la marca de tiempo de la ultima vez que se efectuó una operación INSERT o UPDATE. También se puede establecer una columna de tipo TIMESTAMP a la fecha y hora actual si se le asigna NULL como valor, siempre y cuando no se haya establecido la columna como NOT NULL.
La inicialización y actualización automática de columnas de tipo TIMESTAMP con la fecha y hora actual puede especificarse usando las cláusulas DEFAULT CURRENT_TIMESTAMP o ON UPDATE CURRENT_TIMESTAMP.
Ejemplo:
TIME [ ( fsp ) ]
El tipo de dato TIME nos permite almacenar una hora en los rangos de “-838:59:59.000000” a “838:59:59.000000”. MySQL muestra los valores de tipo TIME en el formato “HH-MM-SS[fracción]” y permite la asignación usando tanto cadenas de texto como números.
El valor opcional fsp en los rangos de 0 a 6 puede ser establecido en pos de especificar la precisión fraccionaria de los segundos. Un valor de cero significa que no hay parte fraccionaria. Si este valor es omitido la precisión por defecto es de cero.
Ejemplo:
YEAR [ ( 4 ) ]
Permite declarar un año en formato de 4 dígitos. MySQL muestra los valores de tipo YEAR en el formato “YYYY”, es decir valores como 1998, 2015 y 0000, y permite la asignación usando tanto cadenas de texto como números.
Ejemplo:
Requerimientos de almacenamiento para cada tipo:
| Tipo de Dato | Tamaño requerido antes de MySQL 5.6.4 | Tamaño requerido a partir de MySQL 5.6.4 |
| YEAR | 1 byte | 1 byte |
| DATE | 3 bytes | 3 bytes |
| TIME | 3 bytes | 3 bytes + almacenamiento fraccionario de los segundos |
| DATETIME | 8 bytes | 5 bytes + almacenamiento fraccionario de los segundos |
| TIMESTAMP | 4 bytes | 4 bytes + almacenamiento fraccionario de los segundos |
En próximos artículos se verán ejemplos prácticos.
Espero que todo cuanto se ha dicho aquí, de una forma u otra le haya servido de aprendizaje, de referencia, que haya valido su preciado tiempo.
Este artículo, al igual que el resto, será revisado con cierta frecuencia en pos de mantener un contenido de calidad y actualizado.
Cualquier sugerencia, ya sea errores a corregir, información o ejemplos a añadir será, más que bienvenida, necesaria!
Fuente:
http://www.wiseratel.com/curso-mysql-tipos-de-datos-de-fecha-y-hora/
Marty Nuemeier (Autor de The Brand Gap)
