MySQL DATETIME explicado: tipo de dato, rango y buenas prácticas

1. ¿Qué es DATETIME en MySQL?

DATETIME de MySQL es un tipo de dato usado para almacenar tanto la fecha como la hora en un solo campo. Gestionar valores de fecha y hora en una base de datos es esencial para muchas aplicaciones, como sistemas de registro y de reservas. El tipo DATETIME almacena una fecha y una hora juntas y admite un amplio rango de valores. Su rango va desde '1000-01-01 00:00:00' hasta '9999-12-31 23:59:59', y también soporta segundos fraccionarios.

2. Visión general de los tipos de datos de fecha y hora en MySQL

2.1 Tipos de datos para manejar fechas y horas

MySQL ofrece los siguientes tipos de datos para trabajar con fechas y horas:

• DATE : Almacena una fecha (año, mes, día). El rango es '1000-01-01' a '9999-12-31'.
• TIME : Almacena solo la hora. El rango es '-838:59:59' a '838:59:59'.
• DATETIME : Almacena tanto la fecha como la hora. El rango es '1000-01-01 00:00:00' a '9999-12-31 23:59:59'.
• TIMESTAMP : Almacena una marca de tiempo UNIX. El rango es '1970-01-01 00:00:01' a '2038-01-19 03:14:07'.

2.2 Diferencias entre DATETIME y TIMESTAMP

DATETIME y TIMESTAMP son similares, pero presentan las siguientes diferencias:

• Zona horaria : DATETIME almacena un valor fijo que no depende de zonas horarias. En cambio, TIMESTAMP se convierte a UTC al almacenarse y se convierte a la zona horaria actual del servidor al recuperarse. Por ello, DATETIME es adecuado para valores de fecha/hora independientes de la zona horaria (por ejemplo, horarios de eventos), mientras que TIMESTAMP es apropiado para datos vinculados a la zona horaria del servidor, como los registros.
• Formato de almacenamiento : DATETIME se guarda tal cual, mientras que TIMESTAMP se guarda como una marca de tiempo UNIX. Como resultado, los valores de TIMESTAMP se ven afectados por la configuración de zona horaria del servidor al representar la hora.

3. Cómo usar DATETIME en MySQL

3.1 Crear una columna DATETIME

Para crear una columna con el tipo DATETIME, use la siguiente sintaxis SQL:

CREATE TABLE sample_table (
    event_time DATETIME
);

En este ejemplo, se crea una columna DATETIME llamada event_time en una tabla llamada sample_table.

3.2 Insertar valores DATETIME

Los valores DATETIME de MySQL pueden insertarse en varios formatos. El formato básico es 'YYYY-MM-DD HH:MM:SS'. Por ejemplo:

INSERT INTO sample_table (event_time) VALUES ('2024-09-16 14:30:00');

También se permiten los siguientes formatos:

• 'YY-MM-DD HH:MM:SS' : Un formato que usa un año de 2 dígitos.
• 'YYYYMMDDHHMMSS' : Un formato sin separadores.

Ejemplo:

INSERT INTO sample_table (event_time) VALUES ('24-09-16 14:30:00');
INSERT INTO sample_table (event_time) VALUES (20240916143000);

Los datos insertados en estos formatos se almacenarán correctamente. Si el año se especifica con dos dígitos, '70-99' se convierte en 1970-1999, y '00-69' se convierte en 2000-2069.

3.3 Recuperar valores DATETIME

Al recuperar valores DATETIME, MySQL los muestra en el formato predeterminado 'YYYY-MM-DD HH:MM:SS'. Por ejemplo:

SELECT event_time FROM sample_table;

Esta consulta muestra los valores de la columna DATETIME usando el formato estándar.

4. Trabajar con segundos fraccionarios

4.1 Precisión de DATETIME

En MySQL, los valores DATETIME pueden incluir segundos fraccionarios. Puede especificar la precisión mediante la opción fsp, que permite almacenar segundos fraccionarios de 0 a 6 dígitos. Por ejemplo, para crear una columna con 3 dígitos de segundos fraccionarios:

CREATE TABLE precise_times (
    event_time DATETIME(3)
);

En este ejemplo, la columna event_time puede almacenar segundos fraccionarios de hasta 3 dígitos.

4.2 Insertar valores con segundos fraccionarios

Para insertar un valor DATETIME que incluya segundos fraccionarios, use lo siguiente:

INSERT INTO precise_times (event_time) VALUES ('2024-09-16 14:30:00.123');

Esta consulta almacena el valor incluyendo segundos fraccionarios con precisión. La parte fraccionaria se almacena sin ser truncada, y la precisión se preserva al recuperar el valor.

5. Mejores prácticas para DATETIME

5.1 Elegir entre DATETIME y TIMESTAMP

• Cuándo usar DATETIME : Para valores de fecha/hora fijos que no dependen de las zonas horarias (por ejemplo, horas de inicio de eventos o fechas de reservas).
• Cuándo usar TIMESTAMP : Para datos de fecha/hora relacionados con la zona horaria del servidor (por ejemplo, marcas de tiempo de creación o actualización de registros).

5.2 Gestión de zonas horarias

Debido a que DATETIME no almacena información de zona horaria, su aplicación debe gestionar las zonas horarias por separado. Por otro lado, TIMESTAMP tiene en cuenta automáticamente la zona horaria del servidor al almacenar y recuperar valores, lo que lo hace adecuado para sistemas que operan en diferentes zonas horarias en todo el mundo.

6. Errores comunes y cómo evitarlos

6.1 Fechas cero y valores inválidos

En MySQL, si intenta insertar un valor DATETIME inválido, se puede almacenar una fecha cero como '0000-00-00 00:00:00'. Dado que esto generalmente no es una fecha válida, debe validar los datos de entrada para evitar que se inserten valores inválidos. Implementar una validación que asegure que la entrada siga el rango y formato correctos puede ayudar a prevenir que se almacenen fechas cero.

6.2 Mal uso de la precisión

Al especificar la precisión de segundos fraccionarios, usar la precisión incorrecta puede producir resultados inesperados. Solo establezca la precisión de segundos fraccionarios cuando sea necesario, y elija el valor fsp con cuidado. Por ejemplo, si su aplicación no requiere precisión subsegundo, no necesita agregar segundos fraccionarios a una columna DATETIME.

7. Resumen

En este artículo, explicamos en detalle el tipo DATETIME de MySQL. DATETIME es un tipo de datos muy útil para almacenar tanto fecha como hora, y es adecuado cuando necesita almacenar valores que no deben verse afectados por las zonas horarias. Al entender las diferencias entre DATETIME y TIMESTAMP, cómo funcionan las zonas horarias y cómo usar segundos fraccionarios, puede gestionar los datos de fecha/hora de manera más efectiva en su base de datos. Además, conocer los errores comunes y cómo evitarlos ayuda a mantener la consistencia y confiabilidad de los datos.

8. Preguntas frecuentes (FAQ)

P1: ¿Cuál es la principal diferencia entre DATETIME y TIMESTAMP?

DATETIME almacena una fecha y hora fijas que no dependen de las zonas horarias. Por ejemplo, es adecuado para almacenar fechas de reservas o horarios de eventos que deben permanecer iguales en cualquier zona horaria. En contraste, TIMESTAMP se almacena en UTC y se convierte a la zona horaria del servidor al recuperarlo. Es adecuado para datos de fecha/hora que dependen de la zona horaria del servidor, como registros.

P2: ¿Cómo puedo almacenar segundos fraccionarios con DATETIME?

Puede establecer la precisión de segundos fraccionarios especificando un valor fsp al crear la columna DATETIME. Por ejemplo, DATETIME(3) almacena segundos fraccionarios hasta 3 dígitos. Use un valor formateado correctamente que incluya segundos fraccionarios al insertar, y se almacenará correctamente.

P3: ¿Debo usar DATETIME o TIMESTAMP?

Depende de su caso de uso. Use DATETIME cuando quiera almacenar una fecha y hora fijas. Use TIMESTAMP para datos de fecha/hora que se ven afectados por la zona horaria del servidor, como tiempos de creación o actualización de registros. Dado que TIMESTAMP realiza una conversión automática de zona horaria, es adecuado cuando su sistema debe operar en diferentes zonas horarias.