Pregunta ¿Debo usar el tipo de datos datetime o timestamp en MySQL?

En MySQL 5 y superior, TIMESTAMP los valores se convierten de la zona horaria actual a UTC para el almacenamiento y se convierten desde la UTC a la zona horaria actual para su recuperación. (Esto ocurre solo para el tipo de datos TIMESTAMP, y no para otros tipos, como DATETIME.)

Por defecto, la zona horaria actual para cada conexión es la hora del servidor. La zona horaria se puede configurar por conexión, como se describe en Soporte de zona horaria del servidor MySQL.

Siempre utilizo los campos DATETIME para cualquier cosa que no sean metadatos de filas (fecha de creación o modificación).

Como mencionado en la documentación de MySQL:

El tipo DATETIME se usa cuando necesita valores que contienen información de fecha y hora. MySQL recupera y muestra los valores DATETIME en el formato 'AAAA-MM-DD HH: MM: SS'. El rango admitido es '1000-01-01 00:00:00' a '9999-12-31 23:59:59'.

...

El tipo de datos TIMESTAMP tiene un rango de '1970-01-01 00:00:01' UTC a '2038-01-09 03:14:07' UTC. Tiene diferentes propiedades, dependiendo de la versión de MySQL y del modo SQL en el que se ejecuta el servidor.

Es muy probable que llegue al límite inferior en TIMESTAMP en uso general, p. almacenamiento de fecha de nacimiento.

Los ejemplos a continuación muestran cómo TIMESTAMP tipo de fecha cambió los valores después de cambiar el time-zone to 'america/new_york' dónde DATETIMEno ha cambiado

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

Convertí mi respuesta en artículo para que más personas puedan encontrar esto útil, MySQL: Datetime Versus Timestamp Tipos de datos.

La principal diferencia es que DATETIME es constante mientras TIMESTAMP se ve afectado por time_zone ajuste.

Por lo tanto, solo importa si tiene, o puede tener en el futuro, clústeres sincronizados en distintas zonas horarias.

En palabras más simples: Si tengo una base de datos en Australia y tomo un volcado de esa base de datos para sincronizar / poblar una base de datos en América, entonces TIMESTAMP se actualizaría para reflejar el tiempo real del evento en la nueva zona horaria, mientras DATETIME aún reflejaría el tiempo del evento en la zona horaria au.

Un buen ejemplo del uso de DATETIME donde debería haberse utilizado TIMESTAMP es en Facebook, donde sus servidores nunca están seguros de qué hora sucedió en las zonas horarias. Una vez tuve una conversación en la que la hora decía que estaba respondiendo mensajes antes de que realmente se enviara el mensaje. (Esto, por supuesto, también podría haber sido causado por una mala traducción de zona horaria en el software de mensajería si los tiempos se publicaron en lugar de sincronizarse).

Tomo esta decisión sobre una base semántica.

Uso una marca de tiempo cuando necesito registrar un punto fijo (más o menos) en el tiempo. Por ejemplo, cuando se insertó un registro en la base de datos o cuando se realizó alguna acción del usuario.

Utilizo un campo de fecha y hora cuando la fecha / hora se puede configurar y cambiar arbitrariamente. Por ejemplo, cuando un usuario puede guardar más adelante, puede cambiar las citas.

TIMESTAMP tiene 4 bytes Vs 8 bytes para DATETIME.

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

Pero al igual que Scronide, dijo que tiene un límite inferior del año 1970. Sin embargo, es genial para cualquier cosa que pueda suceder en el futuro;)

1. TIMESTAMP tiene cuatro bytes frente a ocho bytes para DATETIME.

2. Las marcas de tiempo también son más livianas en la base de datos e indexadas más rápidamente.

3. El tipo DATETIME se usa cuando necesita valores que contienen información de fecha y hora. MySQL recupera y muestra los valores DATETIME en el formato 'AAAA-MM-DD HH: MM: SS'. El rango admitido es '1000-01-01 00:00:00' a '9999-12-31 23:59:59'.

El tipo de datos TIMESTAMP tiene un rango de '1970-01-01 00:00:01' UTC a '2038-01-09 03:14:07' UTC. Tiene diferentes propiedades, dependiendo de la versión de MySQL y del modo SQL en el que se ejecuta el servidor.

4. DATETIME es constante mientras TIMESTAMP se efectúa mediante la configuración time_zone.

Recomiendo usar ninguno un campo DATETIME o TIMESTAMP. Si desea representar un día específico como un todo (como un cumpleaños), utilice un tipo de FECHA, pero si es más específico que eso, probablemente esté interesado en registrar un momento real en lugar de una unidad de hora (día, semana, mes, año). En lugar de usar DATETIME o TIMESTAMP, use BIGINT y simplemente almacene el número de milisegundos desde la época (System.currentTimeMillis () si está usando Java). Esto tiene varias ventajas:

1. Evita el bloqueo de proveedor. Casi todas las bases de datos admiten enteros de una manera relativamente similar. Supongamos que quiere moverse a otra base de datos. ¿Desea preocuparse por las diferencias entre los valores DATETIME de MySQL y cómo los define Oracle? Incluso entre las diferentes versiones de MySQL, TIMESTAMPS tiene un nivel de precisión diferente. Recientemente, MySQL admitió milisegundos en las marcas de tiempo.
2. No hay problemas con la zona horaria Ha habido algunos comentarios interesantes aquí sobre lo que sucede con las zonas horarias con los diferentes tipos de datos. Pero, ¿es este conocimiento común, y todos sus compañeros de trabajo se tomarán el tiempo para aprenderlo? Por otro lado, es bastante difícil equivocarse al cambiar un BigINT en un java.util.Date. El uso de BIGINT causa una gran cantidad de problemas con zonas horarias para quedarse en el camino.
3. No se preocupe por los rangos o la precisión. No tiene que preocuparse por lo que se ve interrumpido por los rangos de fechas futuras (TIMESTAMP solo va a 2038).
4. Integración de herramientas de terceros. Al usar un número entero, es trivial que las herramientas de terceros (por ejemplo, EclipseLink) interactúen con la base de datos. No todas las herramientas de terceros tendrán la misma comprensión de un "datetime" que MySQL. ¿Desea intentar averiguar en Hibernate si debe usar un objeto java.sql.TimeStamp o java.util.Date si está utilizando estos tipos de datos personalizados? El uso de sus tipos de datos básicos hace que el uso con herramientas de terceros sea trivial.

Este problema está estrechamente relacionado con la forma en que debe almacenar un valor monetario (es decir, $ 1,99) en una base de datos. ¿Debería usar un decimal, o el tipo de dinero de la base de datos, o lo peor de todo un doble? Las 3 de estas opciones son terribles, por muchas de las mismas razones enumeradas anteriormente. La solución es almacenar el valor del dinero en centavos usando BIGINT, y luego convertir centavos a dólares cuando muestra el valor al usuario. El trabajo de la base de datos es almacenar datos, y NO interpretar esos datos. Todos estos tipos de datos sofisticados que ve en las bases de datos (especialmente Oracle) agregan poco, y lo inician en el camino hacia el lock-in del proveedor.

Depende de la aplicación, realmente.

Considere establecer una marca de tiempo por un usuario a un servidor en Nueva York, para una cita en Sanghai. Ahora, cuando el usuario se conecta en Sanghai, accede a la misma marca de tiempo de cita de un servidor duplicado en Tokio. Verá la cita en horario de Tokio, contrarrestada con respecto a la hora original de Nueva York.

Entonces, para los valores que representan el tiempo del usuario como una cita o un horario, datetime es mejor. Permite al usuario controlar la fecha y hora exactas deseadas, independientemente de la configuración del servidor. El tiempo configurado es el tiempo establecido, no afectado por la zona horaria del servidor, la zona horaria del usuario o por los cambios en el modo en que se calcula el horario de verano (sí, sí cambia).

Por otro lado, para los valores que representan el tiempo del sistema, como las transacciones de pago, las modificaciones de la tabla o el registro, siempre use marcas de tiempo. El sistema no se verá afectado al mover el servidor a otra zona horaria o al comparar servidores en diferentes zonas horarias.

Las marcas de tiempo también son más livianas en la base de datos e indexadas más rápidamente.

UN timestamp campo es un caso especial de la datetime campo. Puedes crear timestamp columnas para tener propiedades especiales; se puede configurar para que se actualice solo en crear y / o actualizar.

En términos de bases de datos "más grandes", timestamp tiene un par de factores desencadenantes de casos especiales.

Cuál es la correcta depende completamente de lo que quiere hacer.