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

En MySQL 5 y superior, los valores TIMESTAMP se convierten de la zona horaria actual a UTC para su almacenamiento, y se convierten de 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.)

De forma predeterminada, la zona horaria actual para cada conexión es la hora del servidor. La zona horaria se puede establecer por conexión, como se describe en Soporte de Zona Horaria del Servidor MySQL.

Siempre uso campos DATETIME para cualquier cosa que no sea metadatos de fila (fecha de creación o modificación).

Como mencionado en la documentación de MySQL:

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

...

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

Es muy probable que llegue al límite inferior en las marcas de tiempo en uso general e por ejemplo, almacenar la fecha de nacimiento.

Los siguientes ejemplos muestran cómo el tipo de fecha TIMESTAMP cambió los valores después de cambiar el time-zone to 'america/new_york' donde DATETIMEno cambia.

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 |
+---------------------+---------------------+

He convertido mi respuesta en un artículo para que más personas puedan encontrar esto útil, MySQL: Datetime Versus Timestamp Data Types.

La principal diferencia es que DATETIME es constante mientras que TIMESTAMP se ve afectada por la configuración time_zone.

Por lo tanto, solo importa cuando tiene (o puede tener en el futuro) clústeres sincronizados a través de 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 / llenar una base de datos en América, entonces la marca de tiempo se actualizaría para reflejar la hora real del evento en la nueva zona horaria, mientras que DATETIME aún reflejaría la hora del evento en la zona horaria au.

Un gran ejemplo de DATETIME que se utiliza donde se debería haber utilizado la MARCA de TIEMPO es en Facebook, donde sus servidores nunca están muy seguros de qué cosas de hora sucedieron a través de las zonas horarias. Una vez estaba teniendo una conversación en la que el tiempo dijo que estaba respondiendo a los mensajes antes de que el mensaje fuera realmente enviado. (Esto, por supuesto, también podría haber sido causado por una mala traducción de la zona horaria en el software de mensajería si los tiempos se publicaran en lugar que sincronizado.)

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

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

Utilizo un campo datetime cuando la fecha/hora se puede establecer y cambiar arbitrariamente. Por ejemplo, cuando un usuario puede guardar citas de cambio posteriores.

La MARCA DE TIEMPO es de 4 bytes Frente a 8 bytes para DATETIME.

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

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

1. La MARCA DE TIEMPO es de cuatro bytes frente a ocho bytes para DATETIME.

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

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

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

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

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

1. Evita el bloqueo del proveedor. Casi todas las bases de datos soportan enteros de una manera relativamente similar. Supongamos que desea moverse a otra base de datos. ¿Quiere preocuparse por las diferencias entre los valores DATETIME de MySQL y cómo los define Oracle? Incluso entre diferentes versiones de MySQL, las marcas de TIEMPO tienen un nivel diferente de precisión. Fue solo recientemente que MySQL soportó milisegundos en las marcas de tiempo.
2. Sin zona horaria cuestión. Ha habido algunos comentarios perspicaces aquí sobre lo que sucede con las zonas horarias con los diferentes tipos de datos. Pero, ¿es esto de conocimiento común, y todos sus compañeros de trabajo se tomarán el tiempo para aprenderlo? Por otro lado, es bastante difícil estropear el cambio de un BigINT en un java.útil.Fecha. El uso de un BIGINT hace que muchos problemas con las zonas horarias se queden en el camino.
3. No se preocupe por los rangos o la precisión. No tiene que preocuparse por lo que se acorta por los rangos de fechas futuras (MARCA de tiempo solo va a 2038).
4. Integración de herramientas de terceros. Al usar un entero, es trivial que las herramientas de terceros (por ejemplo, EclipseLink) interactúen con la base de datos. No todas las herramientas de terceros van a tener la misma comprensión de un "datetime"como MySQL. Quieres probar y averiguar en Hibernate si debes usar un java.SQL.Marca de tiempo o java.útil.¿Si está utilizando estos tipos de datos personalizados? El uso de los tipos de datos base hace que el uso con herramientas de terceros sea trivial.

Este problema está estrechamente relacionado con cómo debe almacenar un valor monetario (es decir, $1.99) en una base de datos. ¿Debe usar un Decimal, o el tipo de dinero de la base de datos, o lo peor de todo un Doble? Todas estas 3 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 muestre el valor al usuario. El trabajo de la base de datos es almacenar datos, y NO interpretar esos datos. Todos estos los tipos de datos de lujo que ves en las bases de datos(especialmente Oracle) agregan poco y te inician en el camino hacia el bloqueo de proveedores.

Depende de la aplicación, realmente.

Considere establecer una marca de tiempo por un usuario en 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 la cita desde un servidor reflejado en Tokio. Verá la cita en la hora de Tokio, compensada con la hora original de Nueva York.

Entonces, para valores que representan el tiempo del usuario como una cita o un horario, datetime es mejor. Permite al usuario controlar la fecha exacta y tiempo deseado, independientemente de la configuración del servidor. La hora establecida es la hora establecida, no afectada por la zona horaria del servidor, la zona horaria del usuario o por los cambios en la forma en que se calcula el horario de verano (sí, cambia).

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

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

2016 +: lo que aconsejo es establecer su zona horaria Mysql a UTC y usar DATETIME:

Cualquier marco front-end reciente (Angular 1/2, react, Vue,...) puede convertir fácil y automáticamente su fecha y hora UTC a la hora local.

Además:

• DATETIME ahora puede ser automáticamente establecer el valor de tiempo actual ¿Cómo se establece un valor predeterminado para una columna Datetime de MySQL?
• Contrariamente a lo que uno podría pensar, DATETIME es MÁS RÁPIDO QUE LA MARCA DE TIEMPO, http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/
• La marca de tiempo sigue limitada a 1970-2038

(A menos que sea probable que cambie la zona horaria de sus servidores)

Ejemplo con AngularJS

// back-end: format for angular within the sql query
SELECT DATE_FORMAT(my_datetime, "%Y-%m-%dT%TZ")...

// font-end Output the localised time
{{item.my_datetime | date :'medium' }}

Todo el formato de hora localizado está disponible aquí: https://docs.angularjs.org/api/ng/filter/date

Un campo timestamp es un caso especial del campo datetime. Puede crear columnas timestamp para que tengan propiedades especiales; puede configurarse para actualizarse a sí mismo en create y/o update.

En términos de base de datos "más grandes", timestamp tiene un par de disparadores de casos especiales.

Lo correcto depende completamente de lo que quieras hacer.

La marca DE TIEMPO siempre está en UTC (es decir, segundos transcurridos desde 1970-01-01, en UTC), y su servidor MySQL la convierte automáticamente a la fecha/hora de la zona horaria del servidor. A largo plazo, la marca de tiempo es el camino a seguir porque sabes que tus datos temporales siempre estarán en UTC. Por ejemplo, no arruinará sus fechas si migra a un servidor diferente o si cambia la configuración de la zona horaria en su servidor.

Comparación entre DATETIME, TIMESTAMP y DATE

¿Qué es eso [.fracción]?

• Un valor DATETIME o TIMESTAMP puede incluir una fracción final los segundos parten en precisión de hasta microsegundos (6 dígitos). En particular, cualquier parte fraccionaria en un valor insertado en un DATETIME o la columna de marca de TIEMPO se almacena en lugar de descartarse. Esto es opcional.

Fuentes:

• Datos de fecha/hora MySQL referencia de tipos
• Referencia de requisitos de almacenamiento MySQL

Vale la pena señalar que en MySQL puede usar algo en la línea de lo siguiente al crear sus columnas de tabla:

on update CURRENT_TIMESTAMP

Esto actualizará la hora en cada instancia que modifique una fila y a veces es muy útil para la última información de edición almacenada. Esto solo funciona con timestamp, no datetime sin embargo.

Siempre usaría una marca de tiempo Unix cuando se trabaja con MySQL y PHP. La razón principal de esto es que el método predeterminado date en PHP usa una marca de tiempo como parámetro, por lo que no sería necesario analizar.

Para obtener la marca de tiempo Unix actual en PHP, simplemente haga time();
y en MySQL do SELECT UNIX_TIMESTAMP();.

Según mis experiencias, si desea un campo de fecha en el que la inserción ocurre solo una vez y no desea tener ninguna actualización o cualquier otra acción en ese campo en particular, vaya con date time.

Por ejemplo, considere una tabla user con un campo FECHA DE REGISTRO. En esa tabla user, si desea conocer la última hora de inicio de sesión de un usuario en particular, vaya con un campo de tipo timestamp para que el campo se actualice.

Si está creando la tabla desde phpMyAdminla configuración predeterminada actualizará el campo timestamp cuando ocurra una actualización de fila. Si su marca de tiempo archivada no se actualiza con row update, puede usar la siguiente consulta para hacer que un campo timestamp se actualice automáticamente.

ALTER TABLE your_table
      MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

El tipo de datos de marca de tiempo almacena la fecha y la hora, pero en formato UTC, no en el formato de zona horaria actual como lo hace datetime. Y cuando obtiene datos, timestamp vuelve a convertirlo en la hora de la zona horaria actual.

Así que supongamos que usted está en EE.UU. y obtener datos de un servidor que tiene una zona horaria de EE.UU. Luego obtendrá la fecha y la hora de acuerdo con la zona horaria de EE. La columna tipo de datos de marca de tiempo siempre se actualiza automáticamente cuando su fila se actualiza. Por lo tanto, puede ser útil realizar un seguimiento cuando una fila en particular se actualizó la última vez.

Para más detalles puedes leer la entrada del blogFecha Y Hora .

Siempre uso una marca de tiempo Unix, simplemente para mantener la cordura cuando se trata de una gran cantidad de información de fecha y hora, especialmente cuando se realizan ajustes para zonas horarias, agregar/restar fechas, y similares. Al comparar marcas de tiempo, esto excluye los factores que complican la zona horaria y le permite ahorrar recursos en el procesamiento del lado del servidor (Ya sea código de aplicación o consultas de base de datos) en el que hace uso de aritmética ligera en lugar de agregar/restar fecha y hora más pesada función.

Otra cosa que vale la pena considerar:

Si está creando una aplicación, nunca se sabe cómo deben usarse sus datos en el futuro. Si termina teniendo que, por ejemplo, comparar un montón de registros en su conjunto de datos, con, por ejemplo, un montón de elementos de una API de terceros, y decir, ponerlos en orden cronológico, usted estará feliz de tener marcas de tiempo Unix para sus filas. Incluso si decide usar marcas de tiempo MySQL, almacene una marca de tiempo Unix como seguro.

La principal diferencia es

• un ÍNDICE en Timestamp-works
• un ÍNDICE en Datetime - No funciona

Mira este post para ver los problemas con la indexación de Datetime

Tenga cuidado con el cambio de marca de tiempo cuando realiza una instrucción UPDATE en una tabla. Si tiene una tabla con las columnas ' Name '(varchar), 'Age' (int) y 'Date_Added' (timestamp) y ejecuta la siguiente instrucción DML

UPDATE table
SET age = 30

Entonces cada valor en su columna 'Date_Added' se cambiaría a la marca de tiempo actual.

Otra diferencia entre Timestamp y Datetime es en Timestamp que no puede valor predeterminado a NULL.

Encontré una utilidad insuperable en la capacidad de TIMESTAMP para actualizarse automáticamente en función de la hora actual sin el uso de disparadores innecesarios. Eso es solo yo, aunque la marca de TIEMPO es UTC como se dijo.

Puede realizar un seguimiento a través de diferentes zonas horarias, por lo que si necesita mostrar una hora relativa, por ejemplo, la hora UTC es lo que querría.

Referencia tomada de este Artículo:

Las principales diferencias:

MARCA DE TIEMPO utilizada para realizar un seguimiento de los cambios en los registros, y actualizar cada vez que se cambia el registro. DATETIME se utiliza para almacenar valores específicos y estáticos que no se ven afectados por ningún cambio en los registros.

La marca de TIEMPO también se ve afectada por diferentes ajustes relacionados con la ZONA HORARIA. DATETIME es constante.

MARCA de TIEMPO convertida internamente la zona horaria actual a UTC para el almacenamiento y durante la recuperación convertido de nuevo a la zona horaria actual. DATETIME no puede hacer esto.

Rango de marca de tiempo soportado: '1970-01-01 00:00:01 'UTC to' 2038-01-19 03:14:07 ' UTC Rango compatible con DATETIME: '1000-01-01 00:00:00' a '9999-12-31 23:59:59'

Prefiero usar timestamp para mantener todo en un formato raw común y formatear los datos en código PHP o en su consulta SQL. Hay casos en los que es útil en su código para mantener todo en segundos simples.

En mi caso, establezco UTC como zona horaria para todo: el sistema, el servidor de base de datos, etc. cada vez que puedo. Si mi cliente requiere otra zona horaria, la configuro en la aplicación.

Casi siempre prefiero las marcas de tiempo en lugar de los campos de fecha y hora, porque las marcas de tiempo incluyen la zona horaria implícitamente. Por lo tanto, desde el momento en que se accede a la aplicación desde usuarios de diferentes zonas horarias y desea que vean las fechas y horas en su zona horaria local, este tipo de campo hace que es bastante fácil hacerlo que si los datos se guardaran en los campos datetime.

Como un plus, en el caso de una migración de la base de datos a un sistema con otra zona horaria, me sentiría más seguro usando marcas de tiempo. Por no decir posibles problemas al calcular las diferencias entre dos momentos con un cambio de tiempo de sumer entre y que necesitan una precisión de 1 hora o menos.

Así que, para resumir, valoro estas ventajas de timestamp:

• listo para usar en internacional (tiempo múltiple zona) aplicaciones
• migraciones fáciles entre zonas horarias
• bastante fácil de calcular las diferencias (solo resta ambas marcas de tiempo)
• no se preocupe por las fechas de entrada / salida de un período de hora de verano

Por todas estas razones, elijo los campos UTC & timestamp donde sea posible. Y evito dolores de cabeza;)

Me gusta una marca de tiempo Unix, porque puede convertir a números y solo preocuparse por el número. Además de sumar / restar y obtener duraciones, etc. A continuación, convertir el resultado a la fecha en cualquier formato. Este código determina cuánto tiempo en minutos ha pasado entre una marca de tiempo de un documento y la hora actual.

$date  = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now  - $result) / 60);
$min = round($unix_diff_min);

A TIMESTAMP requiere 4 bytes, mientras que a DATETIME requiere 8 bytes.

TIMESTAMP es útil cuando tiene visitantes de diferentes países con diferentes zonas horarias. puede convertir fácilmente la marca de tiempo a cualquier zona horaria del país

Simplemente uso unsigned BIGINT mientras almaceno UTC ...

Que entonces todavía se puede ajustar a la hora local en PHP.

El DATETIME que se seleccionará con FROM_UNIXTIME( integer_timestamp_column ).

Uno obviamente debería establecer un índice en esa columna, de lo contrario no habría ningún avance.