Interroger des dates locales sur des données UTC dans SQL Server

Un rapport sur les commandes du dimanche doit préciser de quel dimanche il parle. Un instant UTC est unique, mais sa date civile dépend du fuseau retenu pour le client ou pour l'entreprise. Appliquer un décalage fixe peut donner des résultats crédibles tout en oubliant des commandes lors du passage à l'heure d'été. Ajouter systématiquement 24 heures au début UTC présente le même défaut.

Une convention pratique consiste à stocker un datetime2 explicitement défini comme UTC, avec un nom tel que OrderedAtUtc. Le type ne contient aucun fuseau et ne garantit pas cette convention. Tous les producteurs doivent la respecter. datetimeoffset conserve un décalage, mais ce décalage ne remplace pas un fuseau nommé et ses règles historiques.

Convertir séparément les deux limites

Pour sélectionner le 10 mars 2024 à Denver, construisons les deux minuits locaux avant de convertir chacun en UTC. L'exemple emploie un identifiant Windows disponible sur l'instance; sys.time_zone_info permet de vérifier les noms reconnus.

DECLARE @LocalDate date = '20240310';
DECLARE @Zone sysname = N'Mountain Standard Time';
DECLARE @StartLocal datetime2(0) = CONVERT(datetime2(0), @LocalDate);
DECLARE @EndLocal datetime2(0) = DATEADD(day, 1, @StartLocal);
DECLARE @StartUtc datetime2(0) = CONVERT(datetime2(0),
    @StartLocal AT TIME ZONE @Zone AT TIME ZONE 'UTC');
DECLARE @EndUtc datetime2(0) = CONVERT(datetime2(0),
    @EndLocal AT TIME ZONE @Zone AT TIME ZONE 'UTC');
SELECT @StartUtc AS StartUtc, @EndUtc AS EndUtc,
       DATEDIFF(hour, @StartUtc, @EndUtc) AS HoursInLocalDay;

Les limites attendues sont le 10 mars à 07:00 UTC et le 11 mars à 06:00 UTC. La journée locale dure donc 23 heures. Le nom contient "Standard Time", mais inclut les règles d'heure d'été. Pour le 3 novembre 2024, le même calcul produit une durée de 25 heures.

Le premier AT TIME ZONE interprète le datetime2 sans décalage comme une heure locale du fuseau indiqué. Le second convertit le datetimeoffset obtenu vers UTC. On peut alors retirer le décalage devenu nul pour comparer le résultat avec une colonne datetime2 dont le contrat est UTC.

Certaines évolutions historiques de fuseaux affectent même minuit. Si une application accepte des rendez-vous saisis en heure locale, elle doit définir le traitement des heures inexistantes et des heures répétées. La résolution automatique de la fonction ne constitue pas forcément la bonne règle métier. Conservez l'instant choisi et le contexte initial nécessaire pour expliquer cette décision.

Garder un intervalle semi-ouvert

La requête compare directement la colonne stockée aux limites déjà calculées.

-- Assumes Orders.OrderedAtUtc contains UTC datetime2 values.
SELECT OrderId, OrderedAtUtc, Total
FROM dbo.Orders
WHERE OrderedAtUtc >= @StartUtc
  AND OrderedAtUtc < @EndUtc;

La borne inférieure est incluse et la borne supérieure exclue. Une commande exactement au minuit suivant appartient ainsi au jour suivant. Cette règle reste correcte si la précision du datetime2 change. Elle évite une fausse dernière heure du jour comme 23:59:59.997, dépendante d'hypothèses sur le type de données.

Un index commençant par OrderedAtUtc peut servir à lire cet intervalle. Lorsque chaque rapport cible un seul locataire, TenantId suivi de OrderedAtUtc peut mieux correspondre au besoin. Le choix dépend des prédicats réels. Convertir chaque valeur stockée dans WHERE complique généralement l'utilisation directe de l'index et répète inutilement la conversion.

Les paramètres doivent avoir des types compatibles avec la colonne. Pour une colonne datetimeoffset, conservez ce type dans les limites au lieu de recopier aveuglément la convention datetime2. Vérifiez aussi que la fin suit le début et refusez les plages démesurées résultant d'une erreur de saisie.

Conserver le sens des données

Ne mélangez pas UTC et heure locale du serveur dans une même colonne. Une conversion ultérieure ne peut pas toujours retrouver la convention d'origine, notamment pendant l'heure répétée en automne. Auditez les importations, les valeurs par défaut et les chemins d'écriture moins fréquents avant de modifier les rapports.

Un rendez-vous futur à 09:00 heure locale pose un autre problème qu'une commande déjà passée. Les règles légales peuvent évoluer. Selon la promesse du produit, conservez l'heure civile souhaitée et le fuseau nommé, puis définissez quand recalculer l'instant UTC. Pour un événement terminé, conservez son instant réel.

Testez les jours ordinaires, les deux changements d'heure, les lignes exactement aux limites et plusieurs fuseaux clients. Comparez les identifiants sélectionnés, pas uniquement les totaux: deux erreurs opposées peuvent se compenser dans une somme. La fiabilité vient d'un contrat temporel cohérent dans toute l'application.

Références techniques: Microsoft Learn: AT TIME ZONE · Microsoft Learn: datetime2.