Dois-je utiliser le type de données datetime ou timestamp dans MySQL ?

Question

Recommanderiez-vous d'utiliser un datetime ou un horodatage et pourquoi (en utilisant MySQL) ?

Je travaille avec PHP du côté serveur.

Answer 1

Les horodatages dans MySQL sont généralement utilisés pour suivre les modifications des enregistrements, et sont souvent mis à jour chaque fois que l'enregistrement est modifié. Si vous souhaitez stocker une valeur spécifique, vous devez utiliser un champ date.

Si vous voulez dire que vous devez choisir entre l'utilisation d'un timestamp UNIX ou d'un champ datetime natif de MySQL, optez pour le format natif. Vous pouvez effectuer des calculs dans MySQL de cette manière ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") et il est simple de changer le format de la valeur en un timestamp UNIX ("SELECT UNIX_TIMESTAMP(my_datetime)") lors de l'interrogation de l'enregistrement si vous voulez opérer sur celui-ci avec PHP.

Answer 2

Dans MySQL 5 et plus, TIMESTAMP sont converties du fuseau horaire actuel en UTC pour le stockage, et reconverties de l'UTC au fuseau horaire actuel pour la récupération. (Ceci ne se produit que pour le type de données TIMESTAMP, et no pour d'autres types tels que DATETIME).

Par défaut, le fuseau horaire actuel pour chaque connexion est l'heure du serveur. Le fuseau horaire peut être défini pour chaque connexion, comme décrit dans la section suivante. Support du fuseau horaire du serveur MySQL .

Answer 3

J'utilise toujours des champs DATETIME pour tout ce qui n'est pas des métadonnées de ligne (date de création ou de modification).

Comme mentionné dans la documentation MySQL :

Le type DATETIME est utilisé lorsque vous avez besoin de valeurs qui contiennent à la fois des informations sur la date et l'heure. MySQL récupère et affiche les valeurs DATETIME au format 'YYYY-MM-DD HH:MM:SS'. La plage prise en charge va de '1000-01-01 00:00:00' à '9999-12-31 23:59:59'.

...

Le type de données TIMESTAMP est compris entre '1970-01-01 00:00:01' UTC et '2038-01-09 03:14:07' UTC. Ses propriétés varient en fonction de la version de MySQL et du mode SQL utilisé par le serveur.

Il est fort probable que vous atteigniez la limite inférieure des TIMESTAMPs utilisés en général -- par exemple pour stocker la date de naissance.

Answer 4

Les exemples ci-dessous montrent comment l TIMESTAMP le type de date a changé les valeurs après avoir modifié le time-zone to 'america/new_york' où DATETIME est inchangé.

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

J'ai converti ma réponse en article afin que plus de personnes puissent trouver cette information utile, MySQL : Types de données Datetime et Timestamp .

Answer 5

La principale différence réside dans le fait que DATETIME est constant alors que TIMESTAMP est affecté par la méthode de calcul de la date. time_zone réglage.

Cela n'a donc d'importance que lorsque vous avez - ou pourriez avoir à l'avenir - des clusters synchronisés sur plusieurs fuseaux horaires.

En termes plus simples : Si j'ai une base de données en Australie, et que je fais une vidange de cette base de données pour synchroniser/peupler une base de données en Amérique, alors le TIMESTAMP sera mis à jour pour refléter l'heure réelle de l'événement dans le nouveau fuseau horaire, tandis que le DATETIME reflétera toujours l'heure de l'événement dans le fuseau horaire de l'Australie. .

Un excellent exemple d'utilisation de DATETIME là où TIMESTAMP aurait dû être utilisé est celui de Facebook, dont les serveurs ne sont jamais tout à fait sûrs de l'heure à laquelle les choses se sont produites dans les différents fuseaux horaires. Une fois, j'ai eu une conversation au cours de laquelle l'heure indiquait que je répondais à des messages avant que le message ne soit réellement envoyé. (Cela aurait pu, bien sûr, être causé par une mauvaise traduction des fuseaux horaires dans le logiciel de messagerie si les heures étaient affichées plutôt que synchronisées).