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

recommanderiez-vous d'utiliser un champ datetime ou un champ timestamp , et pourquoi (en utilisant MySQL)?

je travaille avec PHP du côté serveur.

2350
demandé sur Damjan Pavlica 2009-01-03 19:14:37

30 réponses

Les horodateurs

de MySQL sont généralement utilisés pour suivre les modifications apportées aux 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 datetime.

si vous voulez dire que vous voulez décider entre utiliser un timestamp UNIX ou un champ de DateTime MySQL natif, allez avec le format natif. Vous pouvez faire des calculs dans MySQL de cette façon ("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)") lorsque vous interrogez L'enregistrement si vous voulez l'utiliser avec PHP.

1588
répondu blivet 2018-02-24 04:47:42

Dans MySQL 5 et au-dessus, TIMESTAMP les valeurs sont converties à partir de l'heure actuelle de la zone à l'UTC pour le stockage, et convertie à partir de l'UTC à l'heure actuelle de la zone de récupération. (Ceci se produit seulement pour le type de données TIMESTAMP, et pas pour d'autres types tels que DATETIME.)

par défaut, le fuseau horaire courant pour chaque connexion est l'heure du serveur. Le fuseau horaire peut être réglé sur une base par connexion, comme décrit dans Soutien Du Fuseau Horaire Du Serveur MySQL .

831
répondu Nir 2015-10-05 17:07:54

j'utilise toujours les champs DATETIME pour autre chose que les métadonnées de ligne (date de création ou de modification).

Comme mentionné dans la documentation de MySQL:

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

...

le type de données TIMESTAMP a une plage de '1970-01-01 00:00:01' UTC à '2038-01-09 03:14:07' UTC. Ses propriétés varient en fonction de la version MySQL et du mode SQL dans lequel le serveur tourne.

vous êtes tout à fait susceptible d'atteindre la limite inférieure sur les horodateurs en usage général -- par exemple, stocker date de naissance.

439
répondu scronide 2012-08-10 14:50:14

les exemples ci-dessous montrent comment le type de date TIMESTAMP a changé les valeurs après avoir changé le time-zone to 'america/new_york'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 pour que plus de gens puissent trouver cela utile, MySQL: Datetime Versus Timestamp types de données .

288
répondu mr_eclair 2015-10-05 17:15:13

la principale différence est que DATETIME est constante alors que TIMESTAMP est affectée par le paramètre time_zone .

donc cela n'a d'importance que lorsque vous avez - ou que vous pourriez avoir à l'avenir - synchronisé des clusters à travers les fuseaux horaires.

en termes plus simples: si j'ai une base de données en Australie, et que je jette cette base de données pour synchroniser/peupler une base de données en Amérique, alors L'horodatage serait mis à jour pour refléter le temps réel de l'événement en le nouveau fuseau horaire, alors que DATETIME refléterait encore l'Heure de l'événement dans le fuseau horaire de l'UA .

un bon exemple de DATETIME utilisé là où TIMESTAMP aurait dû être utilisé est sur Facebook, où leurs serveurs ne sont jamais tout à fait sûrs de ce qui s'est passé entre les fuseaux horaires. Une fois j'avais une conversation dans laquelle le temps disait que je répondais aux messages avant que le message ait été réellement envoyé. (Cela, bien sûr, pourrait également avoir été causé par le mauvais fuseau horaire traduction dans le logiciel de messagerie si l'heure était affichée plutôt que synchronisée.)

173
répondu ekerner 2013-01-03 11:34:55

je prends cette décision sur une base sémantique.

j'utilise un horodatage quand j'ai besoin d'enregistrer de la (plus ou moins) point fixe dans le temps. Par exemple, lorsqu'un dossier a été inséré dans la base de données ou lorsqu'une action de l'utilisateur a eu lieu.

j'utilise un champ datetime quand la date/heure peut être définie et changée arbitrairement. Par exemple, quand un utilisateur peut enregistrer plus tard modifier des rendez-vous.

113
répondu MaxVT 2011-01-14 16:10:52

TIMESTAMP est de 4 octets Vs 8 octets pour DATETIME.

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

mais comme scronide dit qu'il a une limite inférieure de l'année 1970. C'est génial pour tout ce qui pourrait arriver dans le futur ;)

89
répondu Alex 2009-01-04 09:00:36
  1. TIMESTAMP est de quatre octets vs huit octets pour le type DATETIME.

  2. Les horodateurs
  3. sont également plus légers sur la base de données et indexés plus rapidement.

  4. le type DATETIME est utilisé lorsque vous avez besoin de valeurs qui contiennent à la fois des informations de date et d'heure. MySQL récupère et affiche les valeurs de DATETIME dans le format ‘YYYY-MM-JJ HH:MM:SS’. La plage prise en charge est '1000-01-01 00:00:00' à '9999-12-31 23: 59: 59'.

le type de données TIMESTAMP a une plage de '1970-01-01 00:00:01' UTC à '2038-01-09 03:14:07' UTC. Ses propriétés varient en fonction de la version MySQL et du mode SQL dans lequel le serveur tourne.

  1. DATETIME est constant alors que le TIMESTAMP est effectuée par le time_zone réglage.
83
répondu Vivek S 2015-10-05 17:32:37

je recommande d'utiliser ni un champ DATETIME ou TIMESTAMP. Si vous voulez représenter un jour spécifique dans son ensemble (comme un anniversaire), alors utilisez un type de DATE, mais si vous êtes plus spécifique que cela, vous êtes probablement intéressé à enregistrer un moment réel par opposition à une unité de temps (Jour,Semaine,Mois,Année). Au lieu d'utiliser un DATETIME ou un TIMESTAMP, utilisez un BIGINT, et stockez simplement le nombre de millisecondes depuis l'époque (système.currentTimeMillis () si vous êtes à l'aide de Java). Cela présente plusieurs avantages:

  1. vous évitez l'immobilisation du vendeur. Pratiquement chaque base de données soutient des entiers de la manière relativement similaire. Supposons que vous vouliez passer à une autre base de données. Voulez-vous vous inquiéter des différences entre les valeurs datetime de MySQL et comment Oracle Les définit? Même parmi les différentes versions de MySQL, les horodateurs ont un niveau de précision différent. Ce N'est que récemment que MySQL a pris en charge les millisecondes dans les horodateurs.
  2. pas de problème de fuseau horaire. Il y a eu quelques commentaires perspicaces sur ce qui se passe avec les timezones avec les différents types de données. Mais est-ce bien commun de connaissances, et vos collègues tous de prendre le temps de l'apprendre? D'un autre côté, c'est assez difficile de changer un BigINT en java.util.Date. L'utilisation D'un BIGINT provoque beaucoup de problèmes avec les fuseaux horaires à tomber au bord du chemin.
  3. aucun souci de portée ou de précision. Vous ne vous inquiétez pas de ce qui est coupé court par les prochaines plages de date (TIMESTAMP va seulement à 2038).
  4. outil Tiers de l'intégration. En utilisant un entier, il est trivial pour les outils tiers (par exemple EclipseLink) pour l'interface avec la base de données. Tous les outils tiers n'auront pas la même compréhension D'un "datetime" que MySQL. Vous voulez essayer de comprendre en hibernation si vous devez utiliser un java.SQL.TimeStamp ou java.util.Objet Date, si vous utilisez ces les types de données personnalisés? L'utilisation de vos types de données de base rend l'utilisation avec des outils tiers trivial.

cette question est étroitement liée à la façon dont vous devriez stocker une valeur monétaire (c.-à-d. 1,99$) dans une base de données. Devez-vous utiliser une décimale, ou le type D'argent de la base de données, ou le pire de tous un Double? Toutes ces trois options sont terribles, pour les mêmes raisons énumérées ci-dessus. La solution est de stocker la valeur de l'argent en cents à L'aide de BIGINT, puis convertir les cents en dollars lorsque vous affichage de la valeur à l'utilisateur. Le travail de la base de données est de stocker des données, et non de les intrepréter. Tous ces types de données fantaisistes que vous voyez dans les bases de données(en particulier Oracle) ajouter peu, et vous Démarrer sur la route à Vendeur lock-in.

80
répondu user64141 2017-12-13 00:44:40

dépend de l'application, vraiment.

envisagez de configurer un timestamp par un utilisateur sur un serveur à New York, pour un rendez-vous à Sanghai. Maintenant que L'utilisateur se connecte à Sanghai, il accède au même timestamp de rendez-vous à partir d'un serveur miroir à Tokyo. Il verra le rendez-vous à L'Heure de Tokyo, décalé de L'heure originale de New York.

donc pour les valeurs qui représentent le temps de l'utilisateur comme un rendez-vous ou un horaire, datetime est mieux. Il permet l'utilisateur de contrôler la date exacte et l'heure souhaitée, indépendamment des paramètres du serveur. L'Heure de paramétrage est l'Heure de paramétrage, non affectée par le fuseau horaire du serveur, le fuseau horaire de l'utilisateur, ou par des changements dans le mode de calcul de l'heure avancée (oui, il change).

par contre, pour les valeurs qui représentent le temps du système comme les transactions de paiement, les modifications de table ou l'enregistrement, utilisez toujours des horodateurs. Le système ne sera pas affectée par le déplacement du serveur à un autre moment zone, ou en comparant entre des serveurs dans des fuseaux horaires différents.

Les horodateurs

sont également plus légers sur la base de données et indexés plus rapidement.

39
répondu ianaré 2010-10-26 21:07:28

2016 + : ce que je vous conseille est de régler votre fuseau horaire Mysql sur UTC et d'utiliser DATETIME:

N'importe quelle charpente frontale récente (angulaire 1/2, react, Vue,...) peut facilement et automatiquement convertir votre UTC datetime en heure locale.

en outre:

(sauf si vous êtes susceptible de changer le fuseau horaire de vos serveurs)


exemple avec 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' }}

disponible ici: https://docs.angularjs.org/api/ng/filter/date

33
répondu Sebastien Horin 2017-08-26 14:54:02
Le champ

A timestamp est un cas spécial du champ datetime . Vous pouvez créer des colonnes timestamp pour avoir des propriétés spéciales; il peut être défini pour se mettre à jour sur create et/ou update.

en termes de "plus grande" base de données, timestamp a un couple de cas spéciaux déclencheurs sur elle.

Que celui de droite est dépend entièrement de ce que vous voulez faire.

32
répondu Jeff Warnica 2016-07-03 18:40:29

TIMESTAMP est toujours en UTC (c'est-à-dire en secondes écoulées depuis 1970-01-01, en UTC), et votre serveur MySQL le convertit automatiquement en date/heure pour le fuseau horaire du serveur. Dans le long terme, TIMESTAMP est la voie à suivre parce que vous savez que vos données temporelles seront toujours en UTC. Par exemple, vous ne gâcherez pas vos dates si vous migrez vers un serveur différent ou si vous changez les paramètres de fuseau horaire de votre serveur.

27
répondu Sobes 2015-10-05 17:10:16

Comparison between DATETIME ,TIMESTAMP and DATE

enter image description here

Qu'est-ce que [.fraction]?

  • DATETIME ou TIMESTAMP valeur peut inclure un trailing fractionnaire seconde partie dans la précision jusqu'à microsecondes (6 chiffres). Dans en particulier, toute partie fractionnaire d'une valeur insérée dans un DATETIME ou la colonne TIMESTAMP est stockée plutôt que jeter. Ceci est bien sûr facultatif.

Sources:

22
répondu jdc91 2017-08-16 06:51:41

il est intéressant de noter dans MySQL vous pouvez utiliser quelque chose le long des lignes du bas lors de la création de vos colonnes de table:

on update CURRENT_TIMESTAMP

cela va mettre à jour l'heure à chaque instance que vous modifiez une ligne et est parfois très utile pour stocker les dernières informations d'édition. Cela ne fonctionne qu'avec timestamp, pas datetime cependant.

21
répondu leejmurphy 2015-10-05 17:21:15

j'utilisais TOUJOURS un timestamp Unix quand je travaillais avec MySQL et PHP. La principale raison en est que la méthode par défaut date en PHP utilise un timestamp comme paramètre, donc il n'y aurait pas d'analyse nécessaire.

pour obtenir le timestamp Unix actuel en PHP, il suffit de faire time();

et dans MySQL do SELECT UNIX_TIMESTAMP(); .

19
répondu Mark Davidson 2015-10-05 17:05:12

D'après mon expérience, si vous voulez un champ date dans lequel l'insertion ne se produit qu'une seule fois et que vous ne voulez pas avoir de mise à jour ou toute autre action sur ce champ particulier, allez avec date time .

par exemple, considérons une table user avec un champ date d'enregistrement . Dans cette table user , si vous voulez connaître le dernier temps enregistré d'un utilisateur particulier, allez avec un champ timestamp tapez pour que le champ soit mis à jour.

si vous créez la table à partir de phpMyAdmin le paramètre par défaut mettra à jour le champ timestamp lorsqu'une mise à jour de ligne se produit. Si votre horodatage n'est pas mis à jour avec row update, vous pouvez utiliser la requête suivante pour faire un champ horodatage get auto updated.

ALTER TABLE your_table
      MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
14
répondu Kannan Prasad 2015-10-05 17:27:43

le type de données timestamp stocke la date et l'heure, mais dans le format UTC, pas dans le format de fuseau horaire courant comme le fait datetime. Et lorsque vous récupérez des données, timestamp les convertit à nouveau dans le fuseau horaire courant.

donc supposons que vous êtes aux États-Unis et que vous obtenez des données d'un serveur qui a un fuseau horaire des États-Unis. Ensuite, vous obtiendrez la date et l'heure selon le fuseau horaire des États-Unis. La colonne de type de données timestamp est toujours mise à jour automatiquement lorsque sa ligne est mise à jour. De sorte qu'il peut être utile pour suivre quand une ligne particulière a été mise à jour la dernière fois.

pour plus de détails, vous pouvez lire l'article de blog Timestamp Vs Datetime .

13
répondu Arvind 2015-10-05 17:30:26

j'utilise toujours un timestamp Unix, simplement pour maintenir la santé lorsque je traite avec beaucoup d'informations datetime, en particulier lorsque je fais des ajustements pour des fuseaux horaires, en ajoutant/soustrayant des dates, et ainsi de suite. Lors de la comparaison des horodateurs, ceci exclut les facteurs de complication du fuseau horaire et vous permet d'épargner des ressources dans votre traitement Côté Serveur (que ce soit des requêtes de code d'application ou de base de données) en ce que vous faites usage de l'arithmétique légère plutôt que plus lourde date-heure ajouter/soustraire des fonctions.

une Autre chose qui vaut la peine d'examiner:

si vous construisez une application, vous ne savez jamais comment vos données pourraient devoir être utilisées en aval. Si vous devez, par exemple, comparer un tas d'enregistrements dans votre ensemble de données, avec, par exemple, un tas d'articles d'une API tierce partie, et par exemple, les mettre dans l'ordre chronologique, vous serez heureux D'avoir des horodateurs Unix pour vos lignes. Même si vous décidez d'utiliser des horodateurs MySQL, stockez un Unix horodatage comme assurance.

12
répondu Oliver Holmberg 2015-10-05 17:19:45

la principale différence est

regardez ce post pour voir les problèmes avec l'indexation Datetime

"
11
répondu Charles Faiga 2017-05-23 12:26:36

méfiez-vous du changement d'horodatage lorsque vous faites une déclaration de mise à jour sur une table. Si vous avez une table avec les colonnes 'Nom' (varchar), 'Age' (int) et 'Date_Added' (timestamp) et que vous exécutez la déclaration DML suivante

UPDATE table
SET age = 30

alors chaque valeur de votre colonne 'Date_Added' sera changée en horodatage courant.

11
répondu Lloyd Banks 2015-10-05 17:51:57

une autre différence entre Timestamp et Datetime est dans Timestamp vous ne pouvez pas valeur par défaut à NULL.

10
répondu ecleel 2014-04-05 14:39:11

j'ai trouvé l'utilité inégalée dans la capacité de TIMESTAMP auto mettre à jour lui-même basé sur le temps actuel sans l'utilisation de déclencheurs inutiles. C'est juste moi cependant, bien que TIMESTAMP soit UTC comme il a été dit.

il peut garder une trace à travers différents fuseaux horaires, donc si vous avez besoin d'afficher un temps relatif par exemple, L'heure UTC est ce que vous voulez.

10
répondu Marc DiMillo 2015-10-05 17:16:08

référence tirée de cet Article:

les principales différences:

TIMESTAMP utilisé pour suivre les changements dans les enregistrements, et mettre à jour chaque fois que l'enregistrement est modifié. DATETIME utilisé pour stocker des valeurs spécifiques et statiques qui ne sont pas affectées par des changements dans les enregistrements.

TIMESTAMP également affecté par différents paramètres liés aux fuseaux horaires. DATETIME est constant.

TIMESTAMP convertie fuseau horaire actuel à l'UTC pour le stockage et lors de la récupération de nouveau converti en le fuseau horaire actuel. DATETIME ne peut pas faire cela.

TIMESTAMP supported range: '1970-01-01 00:00:01' UTC to '2038-01-19 03:14: 07' UTC Gamme prise en charge par DATETIME: '1000-01-01 00:00: 00' à '9999-12-31 23:59:59'

10
répondu Anvesh 2016-02-07 12:49:37

je préfère utiliser timestamp donc pour garder tout dans un format raw commun et formater les données dans le code PHP ou dans votre requête SQL. Il y a des cas où il est pratique dans votre code de garder tout en quelques secondes.

8
répondu Hans 2009-01-04 07:49:18

dans mon cas, J'ai mis UTC comme fuseau horaire pour tout: le système, le serveur de base de données, etc. chaque fois que je le peux. Si mon client a besoin d'un autre fuseau horaire, puis-je le configurer sur l'application.

je préfère presque toujours les horodateurs aux champs datetime, car les horodateurs incluent implicitement le fuseau horaire. Donc, depuis le moment où l'application sera accessible à partir des utilisateurs de différents fuseaux horaires et vous voulez qu'ils voient les dates et les heures dans leur fuseau horaire local, ce type de champ le rend assez facile à faire que si les données ont été enregistrées dans les champs datetime.

en plus, dans le cas d'une migration de la base de données vers un système avec un autre fuseau horaire, je me sentirais plus en confiance en utilisant des horodateurs. Pour ne pas dire les questions possibles lors du calcul des différences entre deux moments avec un changement d'Heure de sumer entre et nécessitant une précision d'une heure ou moins.

donc, pour résumer, j'apprécie ces avantages de timestamp:

  • prêt à utiliser sur l'international (multi fuseau horaire) applications
  • migrations faciles entre les fuseaux horaires
  • assez facile à calculer diférences (il suffit de soustraire les deux horodateurs)
  • ne vous inquiétez pas sur les dates in/out une période d'heure d'été

pour toutes ces raisons, je choisis UTC & timestamp champs où possible. Et j'évite les maux de tête ;)

8
répondu rogerpro 2017-02-24 18:19:01

j'aime un timestamp Unix, parce que vous pouvez convertir en nombres et juste vous soucier du nombre. Plus vous ajoutez / soustrayez et obtenez des durées, etc. Puis convertissez le résultat à la Date dans n'importe quel format. Ce code détermine combien de temps en minutes s'est écoulé entre un horodatage d'un document et l'heure courante.

$date  = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now  - $result) / 60);
$min = round($unix_diff_min);
7
répondu user723220 2015-10-05 17:11:13

A TIMESTAMP nécessite 4 octets, alors qu'un DATETIME nécessite 8 octets.

6
répondu Mwangi Thiga 2016-06-23 14:30:00

TIMESTAMP est utile lorsque vous avez des visiteurs de différents pays avec des fuseaux horaires différents. vous pouvez facilement convertir L'horodatage à n'importe quel pays fuseau horaire

4
répondu Mahdi Jazini 2016-10-27 07:59:30

j'utilise simplement BIGINT non signé pendant le stockage UTC ...

qui peut encore être ajusté à L'heure locale en PHP.

le DATETIME à sélectionner avec FROM_UNIXTIME( integer_timestamp_column ) .

il est évident qu'il faut mettre un index sur cette colonne, sinon il n'y aurait pas d'avance.

4
répondu Martin Zeitler 2016-10-31 00:45:12