MySQL les champs datetime et de l'heure d'été -- comment dois-je faire référence "extra" à l'heure?

Question

Je suis à l'aide de l'Amérique, New York le fuseau horaire. À l'Automne, nous "fall back" d'une heure -- efficacement "gagner" une heure à 2 heures. Au moment de la transition, les actions suivantes se produisent:

il est 01:59:00 -04:00
puis 1 minute plus tard, il devient:
01:00:00 -05:00

Donc, si vous dites simplement "1:30" c'est ambigu quant à savoir si ou non vous faites allusion pour la première fois 1 h 30 autour de rouleaux ou de la deuxième. Je suis en train d'enregistrer les données de planification à une base de données MySQL et ne peut pas déterminer comment économiser le temps correctement.

Voici le problème:
"2009-11-01 00:30:00", est stockée en interne comme 2009-11-01 00:30:00 -04:00
"2009-11-01 01:30:00", est stockée en interne comme 2009-11-01 01:30:00 -05:00

C'est très bien et assez attendu. Mais comment puis-je sauver quoi que ce soit à 01:30:00 -04:00? La documentation ne permet pas de montrer un quelconque soutien pour spécifier le décalage et, en conséquence, quand j'ai essayé de spécifier le décalage de l'été dûment ignoré.

Les seules solutions que j'ai pensé à impliquer la création d'un serveur vers un fuseau horaire qui n'utilise pas l'heure d'été et en faisant les transformations nécessaires dans mes scripts (j'utilise PHP pour cela). Mais cela ne semble pas comme il devrait être nécessaire.

Merci beaucoup pour toutes ces suggestions.

Answer 1

Je l'ai compris à mes besoins. Je vais résumer ce que j'ai appris (désolé, ces notes sont prolixes; ils sont aussi beaucoup pour mon avenir saisine, c'est autre chose).

Contrairement à ce que j'ai dit dans un de mes précédents commentaires, DATETIME et TIMESTAMP champs ne se comportent différemment. Champs d'HORODATAGE (comme les docs indiquer) prendre toutes vous les envoyer dans "YYYY-MM-DD hh:mm:ss" de format et de le convertir à partir de votre fuseau horaire à l'heure UTC. L'inverse se produit de manière transparente à chaque fois que vous récupérer les données. Les champs DATETIME ne pas faire cette conversion. Ils prennent tout ce que vous envoyez et juste de le stocker directement.

Ni la date, ni le champ d'HORODATAGE types fidèle de stocker des données dans un fuseau horaire qui observe l'heure d'été. Si vous le magasin "2009-11-01 01:30:00" les champs n'ont aucun moyen de distinguer la version de 1:30 vous vouliez -- le -04:00 ou -05:00 version.

Ok, donc il faut stocker nos données dans un non fuseau horaire de l'heure d'été (telles que l'UTC). Champs d'HORODATAGE sont incapables de manipuler ces données avec précision pour des raisons que je vais vous expliquer: si votre système est configuré pour un fuseau horaire de l'heure d'été ensuite ce que vous mettez dans HORODATAGE ne peut pas être ce que vous obtenez en retour. Même si vous envoyez des données que vous avez déjà converti à l'UTC, il prendra en charge les données dans votre fuseau horaire local et de faire encore une autre conversion à l'UTC. Cet HORODATAGE de l'exécution locale-à-UTC-back-to-locale aller-retour est de perte de qualité lors de votre fuseau horaire local observe l'heure d'été (depuis "2009-11-01 01:30:00" cartes à 2 fois possible).

Avec DATETIME, vous pouvez stocker vos données dans n'importe quel fuseau horaire vous voulez et être sûr que vous allez obtenir ce que vous l'envoyer (vous n'avez pas forcé les pertes de navette aller-retour pour les conversions de champs d'HORODATAGE imposer sur vous). Donc la solution est d'utiliser un champ DATETIME et avant l'enregistrement dans le champ de convertir à partir de votre système de fuseau horaire, et ce, non l'heure d'été zone que vous souhaitez enregistrer dans (je pense que l'UTC est probablement la meilleure option). Cela vous permet de construire la logique de conversion dans votre langage de script, de sorte que vous pouvez enregistrer explicitement l'UTC équivalent de "2009-11-01 01:30:00 -04:00" ou ""2009-11-01 01:30:00 -05:00".

Une autre chose importante à noter est que MySQL de la date/heure de mathématiques fonctions ne fonctionnent pas correctement autour de l'heure d'été de limites si vous stockez vos dates de l'heure d'été TZ. Donc raison de plus pour économiser de l'UTC.

En un mot, j'ai ceci:

Lors de la récupération des données de la base de données:

Explicitement interpréter les données de la base de données que l'UTC à l'extérieur de MySQL afin d'obtenir une précision timestamp Unix. J'utilise le PHP de la fonction strtotime() fonction ou de sa classe DateTime pour cela. Il ne peut pas être fait de manière fiable à l'intérieur de MySQL utilisation de MySQL CONVERT_TZ() ou UNIX_TIMESTAMP() les fonctions, car CONVERT_TZ ne sortie d'un 'YYYY-MM-DD hh:mm:ss' valeur qui souffre de l'ambiguïté des problèmes, et UNIX_TIMESTAMP() assume son entrée dans le système de fuseau horaire, pas le fuseau horaire les données ont été stockées dans in (UTC).

Lors de l'entreposage de données pour la base de données:

Convertir votre date précise de l'UTC de temps que vous le désirez à l'extérieur de MySQL. Par exemple: avec du PHP de la classe DateTime, vous pouvez spécifier "2009-11-01 1:30:00 est" nettement de "2009-11-01 1:30:00 HAE", puis de le convertir à l'UTC et enregistrer la bonne heure UTC à votre champ DATETIME.

Ouf. Merci beaucoup pour tous les commentaires et de l'aide. J'espère que cela sauve quelqu'un d'autre quelques maux de tête en bas de la route.

BTW, je suis de voir cela sur MySQL 5.0.22 et 5.0.27

Answer 2

MySQL date de types sont, franchement, cassé et ne peut pas stocker toutes les fois correctement, sauf si votre système est définie à une constante de décalage de fuseau horaire, comme l'UTC ou GMT-5. (Je suis en utilisant MySQL 5.0.45)

C'est parce que vous ne pouvez pas stocker tout moment durant l'heure qui précède l'Heure d'été se termine. Peu importe la façon dont vous entrez les dates, toutes les dates de fonction de permettront de traiter ces moments comme si ils sont au cours de l'heure après l'interrupteur.

Mon système de fuseau horaire est - America/New_York. Nous allons essayez de stocker 1257051600 (Sun, 01 Nov 2009 06:00:00 +0100).

Voici l'aide de la propriété de l'INTERVALLE de syntaxe:

SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200

SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200

Même FROM_UNIXTIME() ne reviendront pas l'heure exacte.

SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200

Curieusement, DATETIME sera toujours de stocker et de retour (dans la forme d'une chaîne seulement!) fois à l'intérieur de la "perte" de l'heure lorsque l'heure d'été commence (par exemple, 2009-03-08 02:59:59). Mais l'utilisation de ces dates dans toute MySQL fonction est risqué:

SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599
SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600
# ...
SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600
SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600

La vente à emporter: Si vous avez besoin de stocker et de récupérer tous les temps dans l'année, vous avez un peu d'effets indésirables options:

1. Réglez le système fuseau horaire de GMT + une constante de décalage. E. g. UTC

2. Stocker les dates que INTs (comme Aaron découvert, l'HORODATAGE n'est même pas fiable)

3. Prétendre le type DATETIME est une constante de décalage horaire. E. g. Si vous êtes en America/New_York, convertir votre date GMT-5 en dehors de MySQL, puis le stocker comme un DATETIME (ce qui s'avère être essentiel: voir Aaron réponse). Ensuite, vous devez prendre le plus grand soin à l'aide de MySQL de la date/fonctions de temps, parce que certains supposent que vos valeurs sont du système de fuseau horaire, les autres (esp. le temps des fonctions arithmétiques) sont "timezone agnostique" (ils peuvent se comporter comme si les heures sont au format UTC.

Aaron et je soupçonne que l'auto-génération de colonnes TIMESTAMP sont également cassé. Les deux 2009-11-01 01:30 -0400 et 2009-11-01 01:30 -0500 sera stocké comme l'ambigu 2009-11-01 01:30.

Answer 3

Je pense que micahwittman du lien est la meilleure solution concrète à ces limitations de MySQL: Ensemble de la session fuseau horaire UTC lorsque vous vous connectez:

SET SESSION time_zone = '+0:00'

Ensuite, vous avez juste envoyer des timestamps Unix et tout devrait bien se passer.

Answer 4

Mais comment puis-je sauver quoi que ce soit à 01:30:00 -04:00?

Vous pouvez convertir au format UTC comme:

SELECT CONVERT_TZ('2009-11-29 01:30:00','-04:00','+00:00');

---

Mieux encore, enregistrer les dates sous forme d'un TIMESTAMP champ. C'est toujours stockée dans l'heure UTC et l'heure UTC ne sais pas d'heure été/hiver.

Vous pouvez convertir de l'UTC à localtime à l'aide de CONVERT_TZ:

SELECT CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','SYSTEM');

Où '+00:00' UTC, le fuseau horaire , et le "SYSTÈME" est le fuseau horaire local de l'OS où MySQL fonctionne.

Answer 5

Ce fil m'a fait paniquer puisque nous utilisons TIMESTAMP colonnes avec On UPDATE CURRENT_TIMESTAMP (ie: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) suivi des enregistrements modifiés et ETL pour un datawarehouse.

Dans le cas où quelqu'un me demande, dans ce cas, TIMESTAMP se comporter correctement et vous pouvez faire la distinction entre les deux dates par la conversion de l' TIMESTAMP unix timestamp:

select TestFact.*, UNIX_TIMESTAMP(recordTimestamp) from TestFact;

id  recordTimestamp         UNIX_TIMESTAMP(recordTimestamp)
1   2012-11-04 01:00:10.0   1352005210
2   2012-11-04 01:00:10.0   1352008810