example - tipo de dato timestamp mysql




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

¿Recomendaría usar un campo de datetime y datetime o una datetime , y por qué (con MySQL)?

Estoy trabajando con PHP en el lado del servidor.


2016 + : lo que aconsejo es configurar tu zona horaria Mysql en UTC y usar DATETIME:

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

Adicionalmente:

(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 localizada disponible aquí: https://docs.angularjs.org/api/ng/filter/date


  1. TIMESTAMP 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 y se indexan más rápido.

  3. El tipo DATETIME se usa cuando necesita valores que contienen información de fecha y hora. MySQL recupera y muestra los valores de DATETIME en el formato 'YYYY-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, según 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 de time_zone.

Depende de la aplicación, de verdad.

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 fecha y hora de la cita desde un servidor duplicado en Tokio. Verá la cita en el horario de Tokio, compensado con el horario 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. 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í, sí cambia).

Por otro lado, para los valores que representan la hora del sistema, como transacciones de pago, modificaciones de tabla o registro, siempre use marcas de tiempo. El sistema no se verá afectado al 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 y se indexan más rápido.


Desde mi experiencia, si desea un campo de fecha en el que la inserción se realiza solo una vez y no desea tener ninguna actualización o cualquier otra acción en ese campo en particular, vaya con la fecha y hora .

Por ejemplo, considere una tabla de user con un campo FECHA DE REGISTRO . En esa tabla de user , si desea saber la última vez que inició sesión en un usuario en particular, vaya con un campo de tipo de marca de tiempo para que el campo se actualice.

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

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

En MySQL 5 y versiones posteriores, los valores de TIMESTAMP se convierten de la zona horaria actual a UTC para su almacenamiento, y se vuelven a convertir 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 configurar por conexión, como se describe en MySQL Server Time Zone Support .



Las marcas de tiempo en MySQL se usan generalmente para rastrear los cambios en los registros y, a menudo, se actualizan cada vez que se cambia el registro. Si desea almacenar un valor específico, debe utilizar un campo de fecha y hora.

Si quiso decir que quiere decidir entre usar un sello de hora de UNIX o un campo de fecha y hora de MySQL nativo, vaya con el formato nativo. Puede hacer cálculos dentro de MySQL de esa manera ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") y es sencillo cambiar el formato del valor a una marca de tiempo UNIX ("SELECT UNIX_TIMESTAMP(my_datetime)") cuando consulta el registro Si quieres operarlo con PHP.


Los siguientes ejemplos muestran cómo el tipo de fecha TIMESTAMP cambió los valores después de cambiar la time-zone to 'america/new_york' donde DATETIME 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 |
+---------------------+---------------------+

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


Otra diferencia entre la marca de tiempo y la fecha y la hora está en la marca de tiempo, no se puede establecer un valor predeterminado en NULL.


Siempre usaría una marca de tiempo Unix al trabajar con MySQL y PHP. La razón principal por la que este es el método de date predeterminado en PHP utiliza una marca de tiempo como parámetro, por lo que no se necesita un análisis.

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


Siempre utilizo 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, fechas de adición / sustracción, etc. Al comparar las 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 el código de la aplicación o las consultas de la base de datos), ya que hace uso de la aritmética de peso ligero en lugar de la fecha y hora más pesada. funciones

Otra cosa que vale la pena considerar:

Si está creando una aplicación, nunca sabrá cómo sus datos podrían ser utilizados en la línea. Si terminas teniendo que, digamos, comparar un montón de registros en tu conjunto de datos, con, digamos, un montón de elementos de una API de terceros, y decir, ponerlos en orden cronológico, estarás feliz de tener Marcas de tiempo Unix para tus filas. Incluso si decide utilizar las marcas de tiempo de MySQL, almacene una marca de tiempo de Unix como seguro.



Tenga cuidado con el cambio de fecha y hora cuando realice una instrucción UPDATE en una tabla. Si tiene una tabla con las columnas 'Nombre' (varchar), 'Edad' (int) y 'Fecha_administrada' (marca de tiempo) y ejecuta la siguiente declaración DML

UPDATE table
SET age = 30

luego, cada valor único en su columna 'Fecha_administrada' se cambiará a la marca de tiempo actual.


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

Utilizo una marca de tiempo cuando necesito grabar un punto fijo (más o menos) en el tiempo. Por ejemplo, cuando se insertó un registro en la base de datos o cuando tuvo lugar alguna acción por parte 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 tarde cambiar citas.


Vale la pena señalar que en MySQL puede usar algo como las líneas a continuación al crear sus columnas de tabla:

on update CURRENT_TIMESTAMP

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


Referencia tomada de este artículo:

Las principales diferencias:

TIMESTAMP se utiliza para realizar un seguimiento de los cambios en los registros y actualizar cada vez que se cambia el registro. DATETIME se usa para almacenar un valor específico y estático que no se ve afectado por ningún cambio en los registros.

TIMESTAMP también se ve afectado por diferentes ajustes relacionados con la ZONA HORARIA. DATETIME es constante.

TIMESTAMP convirtió internamente la zona horaria actual a UTC para su almacenamiento, y durante la recuperación se convirtió nuevamente a la zona horaria actual. DATETIME no puede hacer esto.

Rango compatible con TIMESTAMP: '1970-01-01 00:00:01 ′ UTC a' 2038-01-19 03:14:07 'Rango compatible con UTC DATETIME:' 1000-01-01 00:00:00 ′ a '9999 -12-31 23:59:59 ′


En mi caso, establezco UTC como una 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 implícitamente la zona horaria. Por lo tanto, desde el momento en que se accederá 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 sea bastante fácil hacerlo si los datos se guardaron en campos de fecha y hora. .

Como ventaja adicional, 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 hora del sumador en el medio y una precisión de 1 hora o menos.

Entonces, para resumir, valoro estas ventajas de la marca de tiempo:

  • Listo para usar en aplicaciones internacionales (zona horaria múltiple)
  • Migraciones fáciles entre zonas horarias.
  • bastante fácil de calcular diferencias (solo reste ambas marcas de tiempo)
  • No te preocupes por fechas dentro o fuera de un período de verano

Por todas estas razones, elijo los campos UTC y marca de tiempo siempre que sea posible. Y evito dolores de cabeza;)


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

Puede realizar un seguimiento de diferentes zonas horarias, por lo que si necesita mostrar una hora relativa, por ejemplo, la hora UTC es lo que desea.


Prefiero usar la marca de tiempo para mantener todo en un formato común sin formato y formatear los datos en código PHP o en su consulta SQL. Hay casos en los que resulta útil en su código para mantener todo en segundos.


Simplemente uso sin firmar BIGINTal almacenar UTC ...

que luego todavía se puede ajustar a la hora local en PHP.

El DATETIMEa seleccionar con FROM_UNIXTIME( integer_timestamp_column ).

obviamente, uno debería establecer un índice en esa columna, de lo contrario no habría avance.







sqldatatypes