0Pricing
SQL Interview Prep · Leçon

Fuseaux horaires et horodatages

Stocker en UTC, convertir les fuseaux et comprendre les pièges des horodatages soulevés en entretien

Fuseaux horaires et horodatages est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.

Pourquoi les fuseaux horaires déstabilisent les candidats

Les fuseaux horaires sont le point où les candidats sûrs d’eux trébuchent ; les recruteurs les abordent donc pour évaluer la profondeur de leur compréhension. La question centrale est toujours : « comment stocker et comparer des horodatages provenant de plusieurs régions ? »

La réponse professionnelle est une discipline, pas une fonction : stockez tout en UTC, et ne convertissez qu’aux extrémités pour l’affichage. Si le modèle de stockage est correct, la plupart des requêtes deviennent triviales.

  • Horodatage sans fuseau et horodatage avec fuseau
  • Conversion entre les fuseaux
  • UTC comme référence unique

Horodatage sans fuseau et horodatage avec fuseau

PostgreSQL possède deux types d’horodatage, et les confondre est une erreur fréquente en entretien.

  • timestamp (sans fuseau horaire) : une valeur d’horloge locale sans fuseau associé. Elle stocke exactement ce que vous lui fournissez.
  • timestamptz (avec fuseau horaire) : stocké en interne en UTC ; à l’entrée, il est converti depuis le fuseau de la session, et à la sortie, il est reconverti.

Malgré son nom, timestamptz ne stocke pas un fuseau ; il stocke un instant précis en UTC. Ce détail impressionne les recruteurs.

CREATE TABLE events (
  id          bigint,
  occurred_at timestamptz   -- recommended: an absolute instant
);

Stocker en UTC, convertir aux extrémités

La règle d’or. Enregistrez les instants en UTC (utilisez timestamptz) et ne les convertissez vers un fuseau local qu’au moment de les présenter à un utilisateur. Vous éviterez ainsi les ambiguïtés liées aux changements d’heure et garantirez un ordre chronologique correct partout.

Si l’on vous demande « pourquoi UTC ? », répondez que l’UTC n’applique pas de changements d’heure saisonniers : une même heure locale ne se répète donc jamais et n’est jamais sautée, contrairement à l’heure locale.

-- Display a UTC instant in a user's zone (Postgres)
SELECT occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM events;

La double signification de AT TIME ZONE

AT TIME ZONE est ingénieux et constitue un piège fréquent, car il effectue deux opérations opposées selon le type de l'entrée :

  • Appliqué à un timestamptz, il convertit l'instant absolu vers ce fuseau et renvoie un timestamp simple (l'heure civile de ce fuseau).
  • Appliqué à un timestamp simple, il interprète cette heure civile comme appartenant à ce fuseau et renvoie un timestamptz.

Savoir dans quel sens il s'exécute est toute l'astuce.

-- timestamptz -> local wall clock (returns timestamp)
SELECT TIMESTAMPTZ '2024-03-01 12:00:00+00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 21:00:00

-- plain timestamp interpreted in a zone (returns timestamptz)
SELECT TIMESTAMP '2024-03-01 12:00:00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 03:00:00+00

Obtenir l'instant présent

Il faut bien connaître vos fonctions « maintenant ». NOW() et CURRENT_TIMESTAMP renvoient un timestamptz dans Postgres. Elles renvoient l'heure du début de la transaction, et non celle de l'instruction, ce qui est important pour les transactions longues.

Pour obtenir explicitement l'heure UTC, faites la conversion suivante : NOW() AT TIME ZONE 'UTC'. Dans MySQL, UTC_TIMESTAMP() renvoie directement l'heure UTC.

SELECT
  NOW()                       AS tx_start_tz,
  NOW() AT TIME ZONE 'UTC'    AS utc_walltime;

L'heure d'été est le véritable ennemi

Les recruteurs aiment les cas limites liés à DST. Quand les horloges avancent, une heure civile locale n'existe pas ; lorsqu'elles reculent, une heure se répète. Conserver l'heure locale rend ces situations ambiguës ou invalides.

Conserver l'heure UTC évite entièrement ce problème : chaque instant est unique et monotone. Nommer une région comme 'America/New_York' (plutôt qu'un décalage fixe comme -05:00) permet à la base de données d'appliquer correctement les règles de DST pour n'importe quelle date.

-- Region name applies DST automatically for the given date
SELECT TIMESTAMPTZ '2024-07-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS summer, -- EDT (-04)
       TIMESTAMPTZ '2024-01-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS winter; -- EST (-05)

Regrouper par jour local entre plusieurs fuseaux

Voici un problème réaliste : « les utilisateurs actifs quotidiennement dans le fuseau local de chaque utilisateur ». Si vous tronquez directement l'horodatage UTC, les limites de minuit sont incorrectes pour les utilisateurs qui ne sont pas en UTC.

Convertissez l'horodatage dans le fuseau de l'utilisateur avant de le tronquer à la journée. La conversion décale l'heure civile afin que les limites des jours correspondent à l'heure locale.

SELECT
  DATE_TRUNC('day', occurred_at AT TIME ZONE u.tz) AS local_day,
  COUNT(DISTINCT e.user_id)                         AS dau
FROM events e
JOIN users u ON u.id = e.user_id
GROUP BY 1
ORDER BY 1;

Comparer des horodatages sans risque

Lorsque vous filtrez sur une colonne timestamptz, comparez-la à un instant explicite, idéalement à un littéral UTC ou à un timestamptz comportant un décalage. Une comparaison avec une chaîne sans indication de fuseau peut être interprétée dans le fuseau de session, de manière imprévisible.

La comparaison reste ainsi non ambiguë, quelle que soit la personne qui exécute la requête.

SELECT *
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2024-03-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2024-04-01 00:00:00+00';

Époque et horodatages Unix

De nombreux systèmes stockent l'heure sous la forme d'une époque Unix (le nombre de secondes écoulées depuis le 1970-01-01 UTC). Les recruteurs peuvent vous présenter une colonne entière et vous demander de l'interpréter.

  • Postgres : TO_TIMESTAMP(epoch_seconds) renvoie un timestamptz.
  • Pour revenir à l'époque : EXTRACT(EPOCH FROM occurred_at).
  • MySQL : FROM_UNIXTIME() et UNIX_TIMESTAMP().

Les valeurs d'époque sont intrinsèquement en UTC, ce qui explique en partie leur popularité pour le stockage.

SELECT
  TO_TIMESTAMP(1709294400)               AS as_ts,   -- from epoch
  EXTRACT(EPOCH FROM NOW())::bigint        AS as_epoch; -- to epoch

Notes sur les fuseaux horaires selon le dialecte

Voici un aperçu rapide pour montrer que vous êtes à l'aise dans n'importe quel environnement :

  • Postgres : timestamptz + AT TIME ZONE, la prise en charge la plus riche.
  • MySQL : TIMESTAMP effectue automatiquement les conversions via time_zone de la session ; CONVERT_TZ(t, from, to) effectue une conversion explicite. DATETIME ne tient pas compte des fuseaux.
  • SQL Server : datetimeoffset stocke un décalage ; AT TIME ZONE 'name' effectue la conversion à l'aide des noms de fuseaux de Windows.
-- MySQL explicit conversion
SELECT CONVERT_TZ(event_dt, 'UTC', 'Europe/Istanbul') AS local_dt
FROM events;

Exemple approfondi : sessions franchissant minuit

Voici une question d'analyse subtile : compter les sessions pour chaque jour calendaire local lorsqu'une session peut franchir minuit. La solution repose sur la même rigueur : convertir en heure locale, puis regrouper.

Stockez le début et la fin sous forme de timestamptz ; pour l'analyse, déduisez le jour local à partir du début converti. Si une session doit être répartie sur deux jours, vous devrez la relier à un calendrier de jours, un excellent point à soulever en complément.

SELECT
  DATE_TRUNC('day', started_at AT TIME ZONE 'Europe/Istanbul') AS local_day,
  COUNT(*) AS sessions
FROM sessions
GROUP BY 1
ORDER BY 1;

Vérification rapide

Confirmez la stratégie de stockage recommandée et expliquez pourquoi.

Récapitulatif : fuseaux horaires et horodatages

Voici la règle à retenir :

  • Stockez l'UTC sous forme de timestamptz ; ne convertissez vers un fuseau nommé que pour l'affichage.
  • timestamptz stocke un instant UTC, et non un fuseau, malgré son nom.
  • AT TIME ZONE fonctionne dans les deux sens selon le type d'entrée : il convertit un timestamptz en heure civile locale ou interprète un timestamp simple comme appartenant à un fuseau.
  • Utilisez des noms de régions ('America/New_York') afin que DST soit appliqué automatiquement ; évitez les décalages fixes.
  • Convertissez en heure locale avant de tronquer à la journée et comparez les colonnes à des instants UTC explicites.

Questions Fréquemment Posées

La leçon « Fuseaux horaires et horodatages » est-elle gratuite ?

Oui — le texte complet de « Fuseaux horaires et horodatages » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Fuseaux horaires et horodatages » ?

Stocker en UTC, convertir les fuseaux et comprendre les pièges des horodatages soulevés en entretien Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?

Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Fuseaux horaires et horodatages » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?

Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Calcul arithmétique sur les dates et intervalles
  2. Tronquer et regrouper les dates
  3. Analyser et formater des chaînes
  4. Fuseaux horaires et horodatages
← Retour à SQL Interview Prep