Les meilleures pratiques à l'économie de datetime & le fuseau horaire de l'info dans la base de données lorsque des données est la personne à charge sur datetime

Il y avait bien quelques questions à propos de l'enregistrement datetime & fuseaux horaires de l'info dans DB, mais plus sur le plan d'ensemble. Ici, je voudrais aborder un cas spécifique.

Spécifications du système

Nous avons un système de commande de la base de données
C'est un multi-locataire système où les locataires peuvent utiliser arbitraire du fuseau horaire (c'est arbitraire, mais seul fuseau horaire, par le locataire, enregistré dans les Locataires de la table une fois et ne change jamais)

D'affaires de la règle qui doit être couvert en DB

Lorsque le locataire des lieux un Ordre dans le système, numéro de commande obtient calculée sur la base de leurs locaux datetime (ce n'est pas littéralement un nombre, mais une sorte d'identifiant comme ORDR-13432-Year-Month-Day). Calcul précis n'est pas important pour le moment, il est juste important que c'est la personne à charge sur les locataires de locaux datetime
Nous aussi, nous voulons être en mesure de choisir toutes les Commandes, sur le système de niveau, placé entre certains UTC datetimes quel que soit le locataire (pour le régime général des statistiques/reporting)

Notre idée initiale

Notre idée initiale était de sauver UTC datetime à travers toute la DB et, bien sûr, de garder les locataires décalage horaire par rapport à UTC et avoir une application qui consomme DB toujours convertir datetimes à l'UTC, de sorte que DB toujours de fonctionner avec l'UTC.

Approche 1

Économie locale locataires datetime serait bien par le locataire, mais ensuite nous avons problème avec des requêtes comme:
```
SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2
```

C'est problématique parce que OrderDateTime dans cette requête signifie autre moment dans le temps, basée sur le locataire. Bien sûr, cette requête peut inclure joindre à Tenants tableau locaux datetime décalage qui serait alors de calculer OrderDateTime à la volée pour faire des ajustements. C'est possible, mais vous ne savez pas si c'est une bonne façon de le faire?

Approche 2

D'autre part, lors de l'enregistrement de l'UTC datetime, puis, quand nous faisons le calcul de OrderNumber depuis le jour/mois/année à l'UTC peut différer de celui en local datetime

Prenons l'exemple extrême, disons que le locataire est 6 heures à l'avance de l'heure UTC et son datetime local est 2017-01-01 02:00.
UTC serait 2016-12-31 20:00. Commande passée à l'instant devriez obtenir OrderNumber 'ORDR-13432-2017-1-1' mais si l'enregistrement de l'UTC il se ORDR-13432-2016-12-31.

Dans ce cas, au moment de la création de l'Ordre dans la DB, nous devons obtenir de l'UTC datetime, les locataires d'offset et de compiler OrderNumber basé sur recalculé les locataires localtime mais encore sauver la colonne DateTime au format UTC.

Questions

Quel est le meilleur moyen de gérer ce genre de situation?
Est-il une solution sympa à économiser de l'UTC datetimes parce que l'un serait assez gentil pour nous, à cause du système de déclaration?
Si vous allez à l'économie de l'UTC, est l'Approche 2) une bonne façon de gérer les cas ou est-il une meilleure/recommandé?

[Mise à JOUR]

Sur la base des commentaires de Gerard Ashton et Hugo:

Question initiale n'était pas claire en ce qui concerne le détail, si le locataire peut changer le fuseau horaire ou pas et ce qui se passe si l'autorité politique change le fuseau horaire de propriétés ou de certains terirory du fuseau horaire.
Bien sûr, cela est d'une extrême importance, mais elle n'est pas au centre de cette question . On pourrait répondre que, dans une question distincte.

Pour le bien de cette question, permet de supposer locataire ne va pas changer d'emplacement. Le fuseau horaire de propriétés ou de fuseau horaire lui-même pour que le lieu peut changer, et ces changements seront traitées dans le système séparément à partir de cette question.

L'hypothèse que l'heure locale de la zone ne change jamais est risqué. Si le fuseau horaire est stocké comme un nom normalisé (Heure normale de l'est) alors vous devez également garder une trace de début et dates de fin de l'heure d'été à travers l'histoire de la base de données. Si le produit est stocké en tant que numérique offset à partir de l'UTC, il va changer dans les zones touchées par l'heure d'été. En de rares occasions, une subdivision politique peut changer d'un fuseau horaire à un autre.
déclaration que "l'heure locale de la zone ne change jamais" est par locataire. cela signifie simplement qu'une fois que le locataire choisit de leur fuseau horaire, ils utilisent toujours que le fuseau horaire. avec tous les sous-jacents potentiels des changements de fuseau horaire.
Peut-être le moment question de la zone est hors de votre contrôle. Mais si j'étais locataire et le Congrès AMÉRICAIN et de la législature de l'état a décidé de changer le fuseau horaire de mon bureau de la Montagne du Temps à l'Heure Centrale, et le fournisseur de base de données ne serait pas me laisser changer le fuseau horaire dans la base de données, je serais malheureux. Êtes-vous garder une trace du temps de la zone comme un nom ou une valeur numérique?
Même si vous utilisez un nom de fuseau horaire, il n'y a aucune garantie qu'une région sera dans la même zone pour toujours - un nouvelle zone peut être créé pour cette région, ce qui signifie un nouveau nom (qui n'existait pas avant) sera utilisé pour faire référence à la région en termes de règles de zone (ce qui est le décalage entre l'heure UTC, lorsque l'heure d'été commence et se termine, etc) - un nouveau nom est créé lorsque la région décide d'avoir des règles différentes et de ses données historiques, devient différent de capter tous les horaires. Ça n'arrive pas tous les jours, mais vous devez toujours prendre en considération.
Il n'y a rien de mal avec l'aide d'une commande date de la commande. La normalisation ne s'applique pas, c'est surfait de toute façon...

OriginalL'auteur daneejela | 2017-07-07

17

Hugo réponse est la plupart du temps correct, mais je vais ajouter quelques points clés:
- Lorsque vous enregistrez le temps de la zone, ne PAS stocker numérique offset. Comme d'autres l'ont souligné, le décalage entre l'heure UTC est que pour un seul point dans le temps, et peut facilement changement d'heure d'été et pour d'autres raisons. Au lieu de cela, vous devez stocker une heure identificateur de la zone, de préférence une IANA temps identificateur de la zone comme une chaîne de caractères, tels que "America/Los_Angeles". Lire la suite dans le fuseau horaire de la balise wiki.
- Votre OrderDateTime champ doit absolument représentent l'heure UTC. Toutefois, selon votre plateforme de base de données, vous avez plusieurs choix pour comment stocker cette.
  - Par exemple, si vous utilisez Microsoft SQL Server, une bonne approche est de stocker de l'heure locale, dans un datetimeoffset colonne, ce qui préserve le décalage par rapport à UTC. Notez que n'importe quel indice vous créez sur cette colonne sera basée sur l'UTC équivalent, de sorte que vous obtiendrez les bonnes performances de la requête lors de votre gamme de requête.
  - Si vous utilisez d'autres plateformes de base de données, vous pourriez vouloir stocker la valeur UTC dans un timestamp champ. Certaines bases de données ont également timestamp with time zone, mais comprendre que cela ne signifie pas qu'il magasins le fuseau horaire ou de décalage, cela signifie simplement qu'il peut faire des conversions pour vous implicitement comme vous le stocker et récupérer des valeurs. Si vous avez l'intention de toujours représenter UTC, puis souvent timestamp (sans zone de temps) ou tout simplement datetime est plus approprié.
- Depuis l'une des méthodes ci-dessus permettra de stocker une heure UTC, vous aurez aussi besoin de considérer comment effectuer les opérations qui ont besoin d'un indice de l'heure locale de valeurs. Par exemple, vous pourriez avoir besoin pour créer un rapport quotidien, en fonction du jour de la zone. Pour cela, vous aurez besoin de groupe par la date locale. Si vous essayez de calculer que, au moment de la requête à partir de votre valeur UTC, vous finirez par la numérisation de l'ensemble de la table.
  
  Une bonne approche pour régler ce problème est de créer une colonne séparée pour les locaux date (ou peut-être même les locaux datetime en fonction de vos besoins, mais pas un datetimeoffset ou timestamp). Cela pourrait être complètement isolé de la colonne que vous remplir séparément, ou il pourrait être un calculées/colonne calculée en fonction de vos autres colonnes. Utilisez cette colonne dans un index de sorte que vous pouvez filtrer ou d'un groupe par date locale.
- Si vous allez pour les colonnes calculées approche, vous aurez besoin de savoir comment faire pour convertir entre les fuseaux horaires dans la base de données. Certaines bases de données ont un convert_tz fonction intégrée qui comprend l'IANA fuseau horaire identifiants.
  
  Si vous utilisez Microsoft SQL Server, vous pouvez utiliser la nouvelle fuseau HORAIRE fonction en SQL 2016 et Azure SQL DB, mais qui fonctionne uniquement avec Microsoft time zone identifiants. Pour utiliser l'IANA fuseau horaire identifiants, vous aurez besoin d'une solution tierce, comme mon SQL Server en charge des fuseaux horaires projet.
- Au moment de la requête, évitez d'utiliser le BETWEEN déclaration. Il est pleinement inclusive. Il fonctionne bien pour l'ensemble des dates, mais quand vous avez le temps, vous feriez mieux de faire demi-intervalle ouvert de requête, tels que:
```
... WHERE OrderDateTime >= @t1 AND OrderDateTime < @t2
```
  Par exemple, si @t1 ont été le départ d'aujourd'hui, @t2 serait le départ de demain.
Ce qui concerne le scénario discuté dans les commentaires où le temps de l'utilisateur de la zone a changé:
- Si vous choisissez de calculer la date du jour dans la base de données, le seul scénario que vous avez à faire est si une position ou d'affaires des commutateurs de fuseaux horaires sans une "zone de split". Une zone de split est quand un nouveau temps identificateur de la zone est mis en place, qui couvre la zone qui a changé, y compris leurs anciennes et nouvelles règles.
  
  Par exemple, la dernière zone ajoutée à l'IANA tzdb au moment de la rédaction de ce est America/Punta_Arenas, qui était une zone de split lors de la partie sud du Chili a décidé de rester à l'UTC-3 lorsque le reste de Chili (America/Santiago) est allé de nouveau à l'UTC-4, à la fin de l'heure d'été.
  
  Toutefois, si un mineur d'une localité à la frontière de deux fuseaux horaires, décide de changer de quel côté ils se suivent, et une zone de split n'était pas justifié, alors vous auriez éventuellement être en utilisant les règles de leur nouveau fuseau horaire à l'encontre de leurs anciennes données.
- Si vous stockez la date locale séparément (calculée dans l'application, pas l'DB), alors vous n'aurez pas de problèmes. L'utilisateur de modifier leur horaire de la nouvelle, toutes les anciennes données sont encore intacts, et les nouvelles données sont stockées avec le nouveau fuseau horaire.
Réponse impressionnante, comme d'habitude :). Se sent comme ayant séparé locale colonne datetime est le moyen le plus sûr d'aller pour n'importe quel genre de rapports qui nécessite datetime local. À peu près en raison exacte de la synchronisation avec les fuseaux horaires prise d'effet des modifications moment. Séparés de la colonne de gauche pour le client à répondre à ce qu'ils considèrent local qui sonne très juste dans les environnements de serveurs.
Yep, et comme je l'ai dit, j'ai, souvent, il suffit d'utiliser un date colonne pour cela. Sauf si vous faites horaire filtres/grouper par, heure locale, vous avez probablement juste besoin de la date. C'est assez clair ce qui se passe si vous le nom de vos colonnes des choses comme CreatedUTCDateTime et CreatedLocalDate.
Je suppose date est assez bon pour les rapports quotidiens, mais s'il y a des événements qui se tiendra les clients fuseau horaire, par exemple, de l'ordre de l'expiration, de rappel de paiement ou de tout type de futurs événements locaux, puis plein datetime est probablement un meilleur choix. Et depuis sa plus souvent que pas difficile de prédire ce que les clients en fonction de la demande pourrait arriver en bas de la route, c'est probablement plus sûr de aller avec plein soufflé datetime depuis le début.
Désolé, mais je suis en désaccord avec ce point "Quand vous êtes le stockage de la clientèle de la zone de temps, ne PAS stocker numérique, offset... le décalage entre l'heure UTC est que pour un seul point dans le temps, et peut facilement changer". Oui, le décalage est utilisé pour un fuseau horaire pourrait changer. Toutefois, le décalage à un moment donné dans le temps ne jamais changer. Même si vous vous êtes trompé (à dire en fonction de l'emplacement, vous devriez avoir enregistré un +5 compensé mais vous avez enregistré un +6 offset), si le décalage est exacte alors, le temps est exact.
C'est très bien si la date, l'heure et le décalage sont stockés ensemble (comme un datetimeoffset champ dans SQL Server, etc.). Toutefois, s'il est détaché à partir de n'importe quel point dans le temps (comme dans un profil d'utilisateur, ou de l'emplacement du bureau, etc.), puis une fois identificateur de la zone doit être utilisé plutôt que d'un décalage.

OriginalL'auteur Matt Johnson
9

Je vous recommande de toujours utiliser UTC en interne, et de le convertir en un fuseau horaire uniquement lors de l'affichage de la date à l'utilisateur. Donc j'ai tendance à préférer l'approche 2.

Si il y a une règle d'entreprise en disant que le locataire de la date/de l'heure doit être une partie de l'identifiant, ainsi soit-il. Mais en interne, vous gardez la date de la commande en UTC.

À l'aide de votre exemple: un locataire dont le fuseau horaire est en UTC+06:00, de sorte que le locataire de l'heure locale est 2017-01-01 02:00, ce qui est équivalent à 2016-12-31 20:00 en UTC.

L'ordre identificateur serait ORDR-13432-2017-1-1 et l'ordre date serait UTC 2016-12-31 20:00Z.

Pour obtenir toutes les commandes entre 2 dates, cette requête est compliqué:
```
SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2
```
Parce que OrderDateTime est en UTC.

Si vous cherchez pour un locataire, vous pouvez obtenir le fuseau horaire correspondant, convertir la date en conséquence et de recherche pour elle. En utilisant le même exemple que ci-dessus (le locataire du fuseau horaire est en UTC+06:00), pour obtenir toutes les commandes effectuées dans 2017-01-01 (locataire heure locale):
```
--get tenant timezone
--startUTC=tenant's local 2017-01-01 00:00 converted to UTC (2016-12-31T18:00Z)
--endUTC=tenant's local 2017-01-01 23:59:59.999 converted to UTC (2017-01-01T17:59:59.999)
SELECT * FROM ORDERS WHERE OrderDateTime between startUTC and endUTC
```
Cela permet d'obtenir des ORDR-13432-2017-1-1 correctement.

De faire des requêtes pour plusieurs locataires dans des fuseaux horaires différents, les deux approches nécessitent une jointure, pour ne pas "mieux" pour ce cas.

À moins de créer une colonne supplémentaire avec le locataire du local de la date/heure (la UTC OrderDateTime converti locataire du fuseau horaire). Ça va être redondant, mais il peut vous aider avec des requêtes de recherches dans plus d'un fuseau horaire. Si c'est un compromis raisonnable, il dépendra de la fréquence de ces requêtes seront effectuées.

Très belle réponse! Et un bon point d'idée pour économiser colonne supplémentaire avec local de date/heure. Il serait reduntant mais il rend la vie beaucoup plus facile si vous voulez effectuer des requêtes comme: obtenir top 10 des locataires qui ont fait le plus de commandes en une journée (jour défini comme heures de travail, dans leur local de fuseaux horaires).

OriginalL'auteur

Vous devez vous connecter pour publier un commentaire.