tipo example ejemplo dato mysql datetime timestamp sqldatatypes

example - tipo de dato datetime mysql



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

¿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.

Comparación entre DATETIME, TIMESTAMP y DATE

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

  • Un valor de DATETIME o TIMESTAMP puede incluir una parte de segundos fraccionarios de rastreo en una precisión de hasta microsegundos (6 dígitos). En particular, cualquier parte fraccionaria en un valor insertado en una columna DATETIME o TIMESTAMP se almacena en lugar de descartarse. Esto es, por supuesto, opcional.

Fuentes:

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;

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 recuperas datos, la marca de tiempo lo convierte nuevamente en la hora actual de la zona horaria.

Supongamos que está en EE. UU. Y obtiene 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. UU. La columna de tipo de datos de marca de tiempo siempre se actualiza automáticamente cuando se actualiza su fila. Por lo tanto, puede ser útil hacer un seguimiento de cuándo se actualizó una fila en particular la última vez.

Para más detalles puedes leer la publicación del blog Timestamp Vs Datetime .

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 .

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

Por lo tanto, solo importa cuando tiene, o puede tener en el futuro, clusters 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 / llenar una base de datos en Estados Unidos, TIMESTAMP se actualizará para reflejar el tiempo real del evento en la nueva zona horaria, mientras que DATETIME todavía refleja la hora del evento en la zona horaria de au .

Un gran ejemplo del uso de DATETIME donde TIMESTAMP debería haberse usado es en Facebook, donde sus servidores nunca están muy seguros de la hora en que ocurrieron las cosas en las distintas zonas horarias. Una vez tuve una conversación en la que el tiempo decía que estaba respondiendo a los 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 la zona horaria en el software de mensajería si los tiempos se publicaran en lugar de sincronizarse).

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 .

No recomiendo usar un campo DATETIME o TIMESTAMP. Si desea representar un día específico en su totalidad (como un cumpleaños), use un tipo de FECHA, pero si es más específico, probablemente esté 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 la época (System.currentTimeMillis () si está usando Java). Esto tiene varias ventajas:

  1. Usted evita el bloqueo de proveedores. Casi todas las bases de datos admiten enteros de manera relativamente similar. Supongamos que desea moverse a otra base de datos. ¿Quieres preocuparte por las diferencias entre los valores DATETIME de MySQL y cómo los define Oracle? Incluso entre diferentes versiones de MySQL, TIMESTAMPS tiene un nivel diferente de precisión. Solo recientemente, MySQL soportó milisegundos en las marcas de tiempo.
  2. No hay problemas de zona horaria. Ha habido algunos comentarios interesantes sobre lo que sucede con las zonas horarias con los diferentes tipos de datos. ¿Pero es este conocimiento común, y sus compañeros de trabajo se tomarán el tiempo para aprenderlo? Por otro lado, es bastante difícil arruinar el cambio de un BigINT en un java.util.Date. Usar un BIGINT causa muchos problemas con las zonas horarias que se quedan en el camino.
  3. No te preocupes por los rangos o la precisión. No tiene que preocuparse por lo que se corta en los rangos de fechas futuras (TIMESTAMP solo va hasta 2038).
  4. Integración de herramientas de terceros. Al usar un entero, es trivial para las herramientas de terceros (por ejemplo, EclipseLink) para interactuar con la base de datos. No todas las herramientas de terceros tendrán la misma comprensión de "fecha y hora" que MySQL. ¿Quiere probar y averiguar en Hibernate si debe usar un objeto java.sql.TimeStamp o java.util.Date si está usando 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. ¿Debería usar un decimal, o el tipo de dinero de la base de datos, o lo peor de todo, un doble? Todas las 3 de estas opciones son terribles, por muchas de las razones mencionadas anteriormente. La solución es almacenar el valor del dinero en centavos utilizando BIGINT y luego convertir los centavos a dólares cuando se 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 se ven en las bases de datos (especialmente Oracle) agregan poco, y le ayudan a comenzar con el bloqueo del proveedor.

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 los campos DATETIME para cualquier otra cosa que no sean los metadatos de fila (fecha de creación o modificación).

Como se dev.mysql.com/doc/refman/5.1/en/datetime.html 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 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.

Es muy probable que alcance el límite inferior de TIMESTAMP de uso general, por ejemplo, el almacenamiento de la fecha de nacimiento.

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.

TIMESTAMP siempre está en UTC (es decir, segundos transcurridos desde 1970-01-01, en UTC), y su servidor MySQL lo convierte automáticamente a la fecha / hora de la zona horaria del servidor. A largo plazo, TIMESTAMP es el camino a seguir porque sabe que sus datos temporales siempre estarán en UTC. Por ejemplo, no arruinarás las fechas si migras a un servidor diferente o si cambias la configuración de la zona horaria en tu servidor.

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.

Un campo de timestamp es un caso especial del campo de datetime . Puede crear columnas de timestamp para tener propiedades especiales; se puede configurar para actualizarse ya sea en crear y / o actualizar.

En términos de la base de datos "más grande", la timestamp tiene un par de activadores de casos especiales.

Lo que es correcto depende completamente de lo que quieras hacer.

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 ′

A TIMESTAMPrequiere 4 bytes, mientras que a DATETIMErequiere 8 bytes.

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.

Me gusta una marca de tiempo de Unix, porque puedes convertir a números y solo preocuparte por el número. Además, sumas / restas y obtienes duraciones, etc. Luego, convierte el resultado a Fecha en cualquier formato. Este código descubre la cantidad de tiempo en minutos transcurridos 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);

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.

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