Le fuseau horaire de MySQL doit-il être réglé sur UTC ?

Question

Question de suivi de https://serverfault.com/questions/191331/should-servers-have-their-timezone-set-to-gmt-utc

Le fuseau horaire de MySQL doit-il être réglé sur UTC ou doit-il être réglé sur le même fuseau horaire que celui du serveur ou de PHP ? (Si ce n'est pas UTC)

Quels sont les avantages et les inconvénients ?

Answer 1

Il semble que le fuseau horaire du serveur n'ait pas d'importance tant que l'heure est réglée correctement pour le fuseau horaire actuel, que vous connaissez le fuseau horaire des colonnes de date que vous stockez et que vous êtes conscient des problèmes liés à l'heure d'été.

D'autre part, si vous contrôlez les fuseaux horaires des serveurs avec lesquels vous travaillez, vous pouvez tout régler sur UTC en interne et ne jamais vous soucier des fuseaux horaires et de l'heure d'été, du moins lorsqu'il s'agit de stocker l'heure interne.

Voici quelques notes que j'ai rassemblées sur la façon de travailler avec les fuseaux horaires, comme une sorte d'aide-mémoire pour moi-même et pour d'autres, qui pourrait influencer le fuseau horaire que la personne choisira pour son serveur et la façon dont elle stockera la date et l'heure.

Cheatsheet MySQL sur les fuseaux horaires

Notes :

1. Modifier le fuseau horaire ne modifiera pas la date ou l'heure stockée. timestamp mais il sélectionnera une date différente de celle de colonnes d'horodatage

2. Attention ! L'UTC a des secondes intercalaires, qui ressemblent à "2012-06-30 23:59:60" et peuvent être ajoutées de manière aléatoire, avec un préavis de 6 mois, en raison du ralentissement de l'activité économique. être ajoutées de manière aléatoire, avec un préavis de 6 mois, en raison du ralentissement de la rotation de la terre. la rotation de la terre

3. GMT confond les secondes, c'est pourquoi l'UTC a été inventé.

4. Attention ! des fuseaux horaires régionaux différents peuvent produire la même valeur de date en raison d'un décalage horaire. de l'heure d'été

5. La colonne d'horodatage ne prend en charge que les dates comprises entre 1970-01-01 00:00:01 et 2038-01-19 03:14:07 UTC, pour les raisons suivantes une limitation .

6. En interne, un Colonne d'horodatage MySQL est stocké sous la forme UTC mais Lors de la sélection d'une date, MySQL la convertit automatiquement en date de la session en cours. le fuseau horaire de la session en cours.

   Lors de l'enregistrement d'une date dans un horodatage, MySQL suppose que la date est dans le fuseau horaire de la session en cours et la convertit en UTC pour le stockage. stockage.

7. MySQL peut stocker des dates partielles dans des colonnes de type datetime, qui se présentent comme suit "2013-00-00 04:00:00"

8. MySQL stocke "0000-00-00 00:00:00" si vous définissez une colonne de date comme étant NULL, à moins que vous n'ayez spécifiquement défini la colonne pour autoriser lorsque vous la créez.

9. Lire la suite

Pour sélectionner une colonne d'horodatage au format UTC

quel que soit le fuseau horaire de la session MySQL en cours :

SELECT 
CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime` 
FROM `table_name`

Vous pouvez également définir le fuseau horaire du serveur, du global ou de la session en cours sur UTC, puis sélectionner l'horodatage de la manière suivante :

SELECT `timestamp_field` FROM `table_name`

Pour sélectionner la date actuelle en UTC :

SELECT UTC_TIMESTAMP();
SELECT UTC_TIMESTAMP;
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');

Exemple de résultat : 2015-03-24 17:02:41

Pour sélectionner la date actuelle dans le fuseau horaire de la session

SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP();

Pour sélectionner le fuseau horaire défini lors du lancement du serveur

SELECT @@system_time_zone;

Renvoie "MSK" ou "+04:00" pour l'heure de Moscou par exemple, il y a (ou il y a eu) un bogue dans MySQL qui fait que si le décalage est numérique, l'heure d'été n'est pas prise en compte.

Pour obtenir le fuseau horaire actuel

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

Il renverra 02:00:00 si votre fuseau horaire est +2:00.

Pour obtenir l'horodatage UNIX actuel (en secondes) :

SELECT UNIX_TIMESTAMP(NOW());
SELECT UNIX_TIMESTAMP();

Pour obtenir la colonne d'horodatage sous forme d'horodatage UNIX

SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`

Pour obtenir une colonne de date UTC sous forme d'horodatage UNIX

SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`

Obtenir un fuseau horaire courant à partir d'un entier d'horodatage UNIX positif

SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`

Obtenir une date UTC à partir d'un horodatage UNIX

SELECT CONVERT_TZ(FROM_UNIXTIME(`unix_timestamp_int`), @@session.time_zone, '+00:00') 
FROM `table_name`

Obtenir un fuseau horaire courant à partir d'un entier d'horodatage UNIX négatif

SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL -957632400 SECOND) 

Il y a trois endroits où le fuseau horaire peut être défini dans MySQL :

Note : Un fuseau horaire peut être défini dans 2 formats :

1. un décalage par rapport à l'UTC : '+00:00', '+10:00' ou '-6:00'.
2. comme un fuseau horaire nommé : "Europe/Helsinki", "US/Eastern" ou "MET".

Les fuseaux horaires nommés ne peuvent être utilisés que si les tables d'information sur les fuseaux horaires de la base de données mysql ont été créées et renseignées.

dans le fichier "my.cnf"

default_time_zone='+00:00'

o

timezone='UTC'

Variable @@global.time_zone

Pour savoir à quelle valeur ils sont réglés

SELECT @@global.time_zone;

Pour lui attribuer une valeur, utilisez l'un ou l'autre :

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone='+00:00';

Variable @@session.time_zone

SELECT @@session.time_zone;

Pour le régler, utilisez l'un ou l'autre :

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

Les variables "@@global.time_zone variable" et "@@session.time_zone variable" peuvent toutes deux renvoyer "SYSTEM", ce qui signifie qu'elles utilisent le fuseau horaire défini dans "my.cnf".

Pour que les noms de fuseaux horaires fonctionnent (même pour le fuseau horaire par défaut), vous devez configurer vos tables d'information sur les fuseaux horaires : http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Note : vous ne pouvez pas faire cela car cela renverra NULL :

SELECT 
CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime` 
FROM `table_name`

Configuration des tables de fuseaux horaires mysql

Pour CONVERT_TZ pour fonctionner, il faut que les tables de fuseaux horaires soient remplies

SELECT * FROM mysql.`time_zone` ;
SELECT * FROM mysql.`time_zone_leap_second` ;
SELECT * FROM mysql.`time_zone_name` ;
SELECT * FROM mysql.`time_zone_transition` ;
SELECT * FROM mysql.`time_zone_transition_type` ;

S'ils sont vides, remplissez-les en exécutant la commande suivante

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

si cette commande vous donne l'erreur " données trop longues pour la colonne "abréviation" à la ligne 1 ", cela peut être dû à l'ajout d'un caractère NULL à la fin de l'abréviation du fuseau horaire.

la solution consiste à exécuter ceci

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
(if the above gives error "data too long for column 'abbreviation' at row 1")
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql

echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql
cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql

mysql --defaults-file=/etc/mysql/my.cnf --user=verifiedscratch -p mysql < /tmp/mysql_tzinfo_to.sql

(assurez-vous que les règles dst de vos serveurs sont à jour) zdump -v Europe/Moscow | grep 2011 https://chrisjean.com/updating-daylight-saving-time-on-linux/ )

Voir l'historique complet du passage à l'heure d'été pour chaque fuseau horaire

SELECT 
tzn.Name AS tz_name,
tztt.Abbreviation AS tz_abbr,
tztt.Is_DST AS is_dst,
tztt.`Offset` AS `offset`,
DATE_ADD('1970-01-01 00:00:00',INTERVAL tzt.Transition_time SECOND)  AS transition_date
FROM mysql.`time_zone_transition` tzt
INNER JOIN mysql.`time_zone_transition_type` tztt USING(Time_zone_id, Transition_type_id)
INNER JOIN mysql.`time_zone_name` tzn USING(Time_zone_id)
-- WHERE tzn.Name LIKE 'Europe/Moscow' -- Moscow has weird DST changes
ORDER BY tzt.Transition_time ASC

CONVERT_TZ applique également toutes les modifications nécessaires de l'heure d'été en fonction des règles des tableaux ci-dessus et de la date que vous utilisez.

Nota:
Selon la documents La valeur que vous définissez pour time_zone ne change pas, si vous la définissez comme "+01:00" par exemple, la time_zone sera définie comme un décalage par rapport à l'UTC, qui ne suit pas l'heure d'été, et restera donc inchangée toute l'année.

Seules les personnes nommées fuseaux horaires changera d'heure pendant l'heure d'été.

Abréviations telles que CET sera toujours une période hivernale et CEST sera l'heure d'été, tandis que +01:00 sera toujours l'heure d'hiver. UTC + 1 heure et les deux ne changeront pas avec l'heure d'été.

En system timezone sera le fuseau horaire de la machine hôte où mysql est installé (à moins que mysql ne parvienne pas à le déterminer).

Pour en savoir plus sur la collaboration avec DST aquí

Quand ne pas utiliser l'UTC par le légendaire Jon Skeet : https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/ (Par exemple, un événement programmé dans le futur qui représente un moment, et non un instant dans le temps).

questions connexes :

• Comment définir le fuseau horaire de MySQL ?
• MySql - Colonne SELECT TimeStamp au format UTC
• Comment obtenir un horodatage Unix dans MySQL à partir du temps UTC ?
• Conversion de l'horodatage du serveur MySQL en UTC
• https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
• Comment puis-je obtenir le fuseau horaire actuel de MySQL ?
• Champs de date MySQL et heure d'été - comment référencer l'heure "supplémentaire" ?
• Conversion des valeurs négatives à partir de FROM_UNIXTIME

Sources :

• https://bugs.mysql.com/bug.php?id=68861
• http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
• http://dev.mysql.com/doc/refman/5.1/en/datetime.html
• http://en.wikipedia.org/wiki/Coordinated_Universal_Time
• http://shafiqissani.wordpress.com/2010/09/30/how-to-get-the-current-epoch-time-unix-timestamp/
• https://web.ivy.net/~carton/rant/MySQL-timezones.txt

Answer 2

PHP et MySQL ont leurs propres configurations de fuseau horaire par défaut. Vous devez synchroniser l'heure entre votre base de données et votre application web, sinon vous risquez de rencontrer des problèmes.

Lire ce tutoriel : Comment synchroniser les fuseaux horaires de PHP et MySQL

Answer 3

Il s'agit d'un exemple de travail :

jdbc:mysql://localhost:3306/database?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=Europe/Moscow

Answer 4

Les avantages et les inconvénients sont à peu près les mêmes, tout dépend de ce que vous voulez ou non.

Attention, si le fuseau horaire de MySQL diffère de celui de votre système (par exemple PHP), la comparaison de l'heure ou l'impression à l'utilisateur nécessitera quelques manipulations.

Answer 5

Pourquoi ne pas rendre votre application indépendante du fuseau horaire du serveur ?

En raison de l'un ou l'autre de ces scénarios possibles :

• Il se peut que vous n'ayez pas le contrôle des paramètres du fuseau horaire du serveur web/de la base de données.
• Vous risquez de vous tromper et de régler les paramètres de manière incorrecte.
• Il y a tellement de paramètres décrits dans les autres réponses, et tellement de choses à surveiller, que vous risquez de manquer quelque chose
• Une mise à jour du serveur, une réinitialisation du logiciel ou un autre administrateur peut, à son insu, réinitialiser le fuseau horaire du serveur et le remettre à sa valeur par défaut, ce qui a pour effet d'interrompre votre application.

Tous les scénarios ci-dessus donnent lieu à des ruptures dans les calculs de temps de votre application. Il semble donc que la meilleure approche consiste à faire fonctionner votre application indépendamment du fuseau horaire du serveur.

L'idée est simplement de toujours créer les dates en UTC avant de les stocker dans la base de données, et de toujours les recréer à partir des valeurs stockées en UTC également. De cette manière, les calculs de temps ne seront jamais incorrects, car ils sont toujours en UTC. Pour ce faire, il suffit d'indiquer explicitement le paramètre DateTimeZone lors de la création d'un fichier PHP DateTime objet.

D'autre part, la fonctionnalité côté client peut être configurée pour convertir toutes les dates/heures reçues du serveur dans le fuseau horaire du client. Des bibliothèques comme moment.js permettent de le faire très facilement.

Par exemple, lors du stockage d'une date dans la base de données, au lieu d'utiliser la fonction NOW() de MySQL, créez la chaîne d'horodatage en UTC comme suit :

// Storing dates
$date = new DateTime('now', new DateTimeZone('UTC'));
$sql = 'insert into table_name (date_column) values ("' . $date . '")';

// Retreiving dates
$sql = 'select date_column from table_name where condition';
$dateInUTC = new DateTime($date_string_from_db, new DateTimeZone('UTC'));

Vous pouvez définir le fuseau horaire par défaut en PHP pour toutes les dates créées, ce qui évite d'initialiser la classe DateTimeZone à chaque fois que vous souhaitez créer une date.