Stockage de la date et de l'heure en tant qu'UTC en PHP/MySQL

Question

Partout où je lis à propos de la conversion de l'heure dans le fuseau horaire d'un utilisateur, on dit que la meilleure méthode est de stocker une date et une heure en UTC, puis d'ajouter simplement le décalage de fuseau horaire de l'utilisateur à cette heure.

Comment puis-je stocker une date en temps UTC ? J'utilise le champ DATETIME de MySQL.

Lorsque j'ajoute un nouvel enregistrement à MySQL dans mon code PHP, j'utiliserais now() pour insérer dans DATETIME de MySQL.

Aurais-je besoin d'utiliser quelque chose de différent de now() pour stocker l'heure UTC ?

Answer 1

MySQL: UTC_TIMESTAMP()

Renvoie la date et l'heure UTC actuelles en tant que valeur au format 'AAAA-MM-JJ HH:MM:SS' ou format YYYYMMDDHHMMSS.uuuuuu, en fonction du contexte dans lequel la fonction est utilisée, string ou numérique.

PHP: gmdate()

Aussi en PHP, date_default_timezone_set() est utilisée pour définir le fuseau horaire actuel du script. Vous pouvez le définir sur le fuseau horaire du client afin que toutes les fonctions de mise en forme renvoient l'heure dans son fuseau horaire local.

En réalité, j'ai eu du mal à faire fonctionner cela et je tombe toujours dans quelques pièges. Par exemple, les informations de temps renvoyées par MySQL ne sont pas formatées comme 'UTC' donc strtotime les transforme en heure locale si vous n'êtes pas attentif. Je suis curieux de savoir si quelqu'un a une solution fiable pour ce problème, une solution qui ne se casse pas lorsque les dates traversent les frontières des médias (HTTP->PHP->MySQL et MySQL->PHP->HTTP), en tenant compte également de XML et de RSS/Atom.

Answer 2

Je suggérerais d'insérer la date dans le fuseau horaire UTC. Cela vous évitera beaucoup de problèmes à l'avenir avec les changements d'heure.

INSERT INTO abc_table (registrationtime) VALUES (UTC_TIMESTAMP())

Lorsque je interroge mes données, j'utilise le script PHP suivant :

setTimezone(new DateTimeZone('Europe/Istanbul'));
  echo $formatted_date_long=date_format($dt_obj, 'Y-m-d H:i:s');
}
?>

Vous pouvez remplacer la valeur de DateTimeZone par l'un des fuseaux horaires disponibles ici.

Answer 3

https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp

Citation de toute la réponse du lien ci-dessus en cas de suppression :

Pour accompagner le commentaire de @ypercube selon lequel CURRENT_TIMESTAMP est stocké en UTC mais récupéré dans le fuseau horaire actuel, vous pouvez affecter le paramètre du fuseau horaire de votre serveur avec l'option --default_time_zone pour la récupération. Cela vous permet d'avoir une récupération toujours en UTC.

Par défaut, l'option est 'SYSTEM' qui est la façon dont le fuseau horaire de votre système est défini (qui peut être ou non UTC) :

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 16:28:45 |
+---------------------+
1 row in set (0.00 sec)

Vous pouvez définir cela dynamiquement :

mysql> SET @@session.time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

Ou définitivement dans votre my.cnf :

[mysqld]
**autres variables**
default_time_zone='+00:00'

Redémarrez votre serveur, et vous verrez le changement :

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 20:27:50 |
+---------------------+
1 row in set (0.01 sec)

Answer 4

NOW() vous donne l'heure (y compris le décalage de fuseau horaire) du système exécutant votre base de données. Pour obtenir la date/heure UTC, vous devriez utiliser UTC_TIMESTAMP() comme décrit dans le Manuel de référence MySQL.

Answer 5

Comme le suggère @Haluk, vous pouvez stocker la date sous forme de datetime UTC. Je complète sa réponse pour les situations où la date que vous voulez insérer provient du code PHP, et j'ajoute quelques détails sur son fonctionnement :

$pdo = new PDO('mysql:host=mydbname.mysql.db;dbname=mydbname', 'monutilisateur', 'monmotdepasse');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$insertStmt = $pdo->prepare("insert into test (last_modified)"
    ." values(:last_modified)");
$insertStmt->bindParam(':last_modified', $lastModified, PDO::PARAM_STR);
$lastModified = gmdate("Y-m-d H:i:s");
$insertStmt->execute();

En réalité, en PHP, datetime n'a pas de fuseau horaire associé. Ce qui est passé à PDO est juste une chaîne de caractères, dans l'un des formats reconnus par MySQL, représentant la date dans le fuseau horaire UTC. Par exemple, si la date est 2015-10-21 19:32:33 UTC+2:00, le code ci-dessus dit simplement à MySQL d'insérer 2015-10-21 17:32:33. gmdate("Y-m-d H:i:s") vous donne la date actuelle dans ce format. Dans tous les cas, il vous suffit de construire la chaîne représentant la date que vous souhaitez insérer en temps UTC.

Ensuite, lorsque vous lisez la date, vous devez indiquer à PHP que le fuseau horaire de la date que vous venez de récupérer est UTC. Utilisez le code dans la réponse de Haluk pour cela.