Champs datetime MySQL et heure avancée - comment faire référence à l'heure" supplémentaire"?

j'ai tout prévu. Je vais résumer ce que j'ai appris (désolé, ces notes sont verbeuses; elles sont autant pour ma future référence que pour toute autre chose).

contrairement à ce que j'ai dit dans un de mes commentaires précédents, DATETIME et TIMESTAMP champs do se comportent différemment. Champs TIMESTAMP (comme les docs l'indiquent) prenez ce que vous leur envoyez dans le format "AAAA-MM-JJ hh:mm:ss" et convertissez-le de votre fuseau horaire actuel à l'heure UTC. L'inverse se produit de manière transparente chaque fois que vous récupérez les données. Les champs DATETIME ne font pas cette conversion. Ils prennent tout ce que vous leur envoyez et le stockent directement.

ni les types de champ DATETIME ni les types de champ TIMESTAMP ne peuvent stocker avec précision des données dans un fuseau horaire qui observe DST . Si vous stockez "2009-11-01 01:30:00" les champs n'ont aucun moyen de distinguer quelle version de 1:30am vous vouliez -- la version -04:00 ou -05:00.

Ok, donc nous devons stocker nos données dans un fuseau horaire non DST (comme UTC). Les champs TIMESTAMP sont incapables de gérer ces données avec précision pour des raisons que j'expliquerai: si votre système est réglé sur un DST timezone, alors ce que vous mettez dans TIMESTAMP peut ne pas être ce que vous obtenez de nouveau. Même si vous lui envoyez des données que vous avez déjà converties en UTC, il supposera que les données sont dans votre fuseau horaire local et fera une autre conversion en UTC. Cet HORODATAGE de l'exécution locale-à-UTC-back-to-locale aller-retour est losy lorsque votre fuseau horaire local observe DST (depuis "2009-11-01 01:30:00" cartes à 2 différentes heures possibles).

avec DATETIME vous pouvez stocker vos données dans n'importe quel fuseau horaire que vous voulez et être sûr que vous obtiendrez ce que vous lui envoyez (vous ne vous êtes pas forcé dans les conversions de Round Trip lossy que les champs de TIMESTAMP vous font). La solution est donc d'utiliser un champ DATETIME et avant d'enregistrer sur le champ convertir de votre fuseau horaire système en quelle que soit la zone non-DST dans laquelle vous voulez le sauvegarder (je pense que 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 les fonctions date/time maths de MySQL ne fonctionnent pas correctement autour des limites DST si vous stockez vos dates dans une DST TZ. Raison de plus pour économiser L'UTC.

en un mot je fais maintenant ceci:

lors de l'extraction des données de la base de données:

interprète explicitement les données de la base de données comme UTC en dehors de MySQL afin d'obtenir une estampille temporelle Unix précise. J'utilise la fonction strtotime() de PHP ou sa classe DateTime pour cela. Cela ne peut pas être fait de manière fiable à L'intérieur de MySQL en utilisant les fonctions CONVERT_TZ() ou UNIX_TIMESTAMP() de MySQL parce que CONVERT_TZ ne produira qu'un ' AAAA-MM-JJ hh:mm: ss' qui souffre de problèmes d'ambiguïté, et UNIX_TIMESTAMP() suppose que son entrée est dans le fuseau horaire du système, et non dans le fuseau horaire dans lequel les données ont été stockées (UTC).

lors du stockage des données dans la base de données:

Convertissez votre date à l'heure UTC précise que vous désirez en dehors de MySQL. Par exemple: avec la classe DateTime de PHP vous pouvez spécifier "2009-11-01 1: 30:00 EST" distinctement de "2009-11-01 1:30: 00 EDT", puis le convertir en UTC et enregistrez L'heure UTC correcte dans votre champ DATETIME.

Phew. Merci beaucoup pour tous les commentaires et de l'aide. J'espère que ça évitera à quelqu'un d'autre des maux de tête plus tard.

BTW, je le vois sur MySQL 5.0.22 et 5.0.27

je pense que le link de micahwittman a la meilleure solution pratique à ces limitations MySQL: Réglez le fuseau horaire de la session sur UTC lorsque vous vous connectez:

SET SESSION time_zone = '+0:00'

ensuite, il suffit de lui envoyer des horodateurs Unix et tout devrait être parfait.

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

vous pouvez convertir en UTC comme:

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

---

Mieux encore, enregistrez les dates dans un champ TIMESTAMP . C'est toujours stocké dans UTC, et UTC n'est pas au courant de l'heure d'été/d'hiver.

vous pouvez convertir UTC en localtime en utilisant CONVERT_TZ :

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

où '+00: 00' Est UTC, le fuseau horaire de from , et 'SYSTEM' est le fuseau horaire local de L'OS où MySQL s'exécute.

ce fil m'a fait freak puisque nous utilisons TIMESTAMP colonnes avec On UPDATE CURRENT_TIMESTAMP (i.e.: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) pour suivre les enregistrements changés et ETL à un datawarehouse.

dans le cas où quelqu'un se demande, dans ce cas, TIMESTAMP se comporter correctement et vous pouvez différencier entre les deux dates similaires en convertissant le TIMESTAMP à l'horodatage unix:

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

je travaillais sur la journalisation des comptes de visites de pages et l'affichage des comptes dans le graphe (en utilisant Flot jQuery plugin). J'ai rempli le tableau avec des données de test et tout avait l'air bien, mais j'ai remarqué qu'à la fin du graphique les points étaient un jour de congé selon les étiquettes sur X-axis. Après examen, j'ai remarqué que le nombre de vues pour le jour 2015-10-25 a été récupéré deux fois de la base de données et passé à Flot, donc chaque jour après cette date a été déplacé d'un jour à droite.

Après avoir cherché un bug dans mon code pendant un certain temps, j'ai réalisé que cette date est quand le DST a lieu. Puis je suis venu à cette SORTE de page...

...mais les solutions suggérées étaient exagérées pour ce dont j'avais besoin ou elles avaient d'autres inconvénients. Je ne suis pas très inquiet de ne pas pouvoir distinguer entre des horodateurs Ambigus. j'ai juste besoin de compter et d'afficher les enregistrements par jours.

D'abord, je récupère la plage de dates:

SELECT 
    DATE(MIN(created_timestamp)) AS min_date, 
    DATE(MAX(created_timestamp)) AS max_date 
FROM page_display_log
WHERE item_id = :item_id

puis, dans une boucle for, à partir de min_date , se terminant par max_date , par étape d'un jour ( 60*60*24 ), je récupère les comptes:

for( $day = $min_date_timestamp; $day <= $max_date_timestamp; $day += 60 * 60 * 24 ) {
    $query = "
        SELECT COUNT(*) AS count_per_day
        FROM page_display_log
        WHERE 
            item_id = :item_id AND
            ( 
                created_timestamp BETWEEN 
                '" . date( "Y-m-d 00:00:00", $day ) . "' AND
                '" . date( "Y-m-d 23:59:59", $day ) . "'
            )
    ";
    //execute query and do stuff with the result
}

Mon définitif et rapide de la solution à mon problème était:

$min_date_timestamp += 60 * 60 * 2; // To avoid DST problems
for( $day = $min_date_timestamp; $day <= $max_da.....

Donc je suis de ne pas regarder la boucle dans le début de la journée, mais deux heures plus tard . La journée est toujours la même chose, et je suis toujours en train de récupérer des comptes corrects, puisque je demande explicitement à la base de données des enregistrements entre 00:00:00 et 23:59:59 de la journée, indépendamment de l'heure réelle de l'horodatage. Et quand le temps saute d'une heure, je suis toujours dans le bon jour.

Note: je sais qu'il s'agit d'un fil de 5 ans, et je sais que ce n'est pas une réponse à la question OPs, mais il pourrait aider les gens comme moi qui ont rencontré cette page à la recherche de solution au problème I décrire.