MySQL : utilisation et amélioration de `DATETIME` et `TIMESTAMP`

Introduction

Dans MySQL, DATETIME et TIMESTAMP sont des types de données couramment utilisés pour stocker des valeurs temporelles. Bien que les deux servent à stocker la date et l’heure, il existe des différences fondamentales entre eux qui affectent leur utilisation. Cet article explorera les différences entre DATETIME et TIMESTAMP, et comment surmonter certaines limitations de DATETIME pour tirer parti des avantages de TIMESTAMP.

Différences entre DATETIME et TIMESTAMP

Gamme:

• DATETIME : peut stocker des valeurs allant de « 1000-01-01 00:00:00 » à « 9999-12-31 23:59:59 ».
• TIMESTAMP : peut stocker des valeurs allant de « 1970-01-01 00:00:01 » à « 2038-01-19 03:14:07 ».

Stockage:

• DATETIME : stocké tel quel, au format date et heure, sans dépendre du fuseau horaire. Il nécessite 8 octets de stockage.
• TIMESTAMP : stocké sous forme d'entier représentant le nombre de secondes depuis le 01/01/1970 à 00:00:00 UTC. Il nécessite 4 octets de stockage.

Fuseau horaire:

• DATETIME : ne dépend pas du fuseau horaire et stocke la valeur telle quelle.
• TIMESTAMP : affecté par le fuseau horaire actuel du serveur MySQL. Lors de l'insertion ou de la récupération de valeurs, elles sont automatiquement converties entre l'heure locale et UTC.

Mises à jour automatiques :

• DATETIME : ne prend pas en charge les mises à jour automatiques directement lorsqu'une ligne est modifiée.
• TIMESTAMP : peut être configuré pour mettre à jour automatiquement la valeur lorsqu'une ligne est modifiée à l'aide des options DEFAULT CURRENT_TIMESTAMP et ON UPDATE CURRENT_TIMESTAMP.

Amélioration de l'utilisation de DATETIME

Pour surmonter certaines limitations du type de données DATETIME, vous pouvez suivre ces stratégies :

1. Normalisation du fuseau horaire

Pour résoudre le problème de DATETIME qui n'adhère pas au fuseau horaire, vous pouvez standardiser toutes les opérations temporelles au niveau de la base de données et de l'application pour utiliser UTC.

Configuration de la base de données pour qu'elle fonctionne en UTC :

SET GLOBAL time_zone = '+00:00';
SET time_zone = '+00:00';

Conversion de valeurs en UTC lors de l'insertion :

INSERT INTO example_table (event_time) VALUES (CONVERT_TZ('2024-06-19 12:30:00', 'Your/Timezone', '+00:00'));

Conversion des valeurs UTC en heure locale lors de la récupération :

SELECT CONVERT_TZ(event_time, '+00:00', 'Your/Timezone') as local_event_time FROM example_table;

2. Mises à jour automatiques

Pour mettre à jour automatiquement les valeurs DATETIME lorsqu'une ligne est modifiée, vous pouvez utiliser des déclencheurs.

Création d'un déclencheur pour mettre à jour le champ lors de la modification :

CREATE TRIGGER before_update_example_table
BEFORE UPDATE ON example_table
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END;

3. Utilisation des valeurs par défaut

Pour définir automatiquement les valeurs DATETIME lors de l'insertion, vous pouvez attribuer des valeurs par défaut à l'aide de la fonction NOW().

Définition des champs par défaut :

CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4. Précision de manipulation

Si une plus grande précision pour les heures est nécessaire, vous pouvez utiliser DATETIME(6) ou TIMESTAMP(6) pour stocker les heures jusqu'à une précision de la microseconde.

Création d'un tableau avec une précision à la microseconde :

CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

5. Délais de traitement dans la candidature

Assurez-vous que votre application gère correctement les heures en convertissant toutes les heures en UTC avant de les stocker et en les reconvertissant en heure locale lors de leur affichage à l'utilisateur.

Exemple en PHP :

// Setting the application time zone to UTC
date_default_timezone_set('UTC');

// Storing the current time as UTC
$current_time_utc = date('Y-m-d H:i:s');
$query = "INSERT INTO example_table (event_time) VALUES ('$current_time_utc')";

// Retrieving the time and converting it to local time
$event_time_utc = '2024-06-19 12:30:00';
$event_time_local = new DateTime($event_time_utc, new DateTimeZone('UTC'));
$event_time_local->setTimezone(new DateTimeZone('Your/Timezone'));
echo $event_time_local->format('Y-m-d H:i:s');

Résumé

• DATETIME : utilisé pour stocker les dates et les heures sans dépendances de fuseau horaire. Convient pour des événements futurs ou un calendrier fixe.
• TIMESTAMP : utilisé pour suivre le temps par rapport au fuseau horaire actuel. Convient pour enregistrer l'heure à laquelle les données sont insérées ou mises à jour.

Amélioration de DATETIME

• Standardisation du fuseau horaire : utilisez UTC pour standardiser les heures.
• Mises à jour automatiques : utilisez des déclencheurs pour mettre à jour les champs automatiquement.
• Valeurs par défaut : définissez les valeurs par défaut pour le champ DATETIME.
• Précision : utilisez DATETIME(6) pour une plus grande précision.
• Gestion des heures dans l'application : convertissez correctement les heures entre UTC et l'heure locale.

En suivant ces stratégies, vous pouvez tirer parti des avantages de TIMESTAMP tout en utilisant DATETIME, ce qui facilite la gestion efficace et efficiente des valeurs temporelles.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!