Date & Time dans BigQuery

La manipulation des données temporelles est l'un des défis les plus courants et les plus redoutables. Entre fuseaux horaires, changements d'heure et performances dégradées, les pièges sont nombreux. Voici comment les éviter et optimiser vos requêtes BigQuery.

Les Différents Types de Temps

BigQuery a quatre types de temps principaux. La chose la plus importante est de bien faire la différence entre une heure locale (DATETIME) et un moment unique dans le monde entier (TIMESTAMP).

Type	Ce que c'est	Exemple de Format	Fuseau Horaire
DATE	Juste le jour, le mois et l'année.	`2025-10-07`	sans
TIME	Juste l'heure de la journée.	`14:30:00`	sans
DATETIME	La date et l'heure locales (civiles).	`2025-10-07 14:30:00`	sans
TIMESTAMP	Le moment exact et unique où un événement s'est produit sur Terre. Stocké en temps universel (UTC).	`2025-10-07 12:30:00+02`	avec

Pour tout événement important (une vente, un clic, une connexion), utilisez toujours TIMESTAMP. Cela garantit que l'heure est la même pour tout le monde, peu importe où il se trouve.

Les Erreurs Coûteuses à Éviter

1. Le Piège Silencieux du Fuseau Horaire

La plus grosse erreur est de mélanger le temps local (DATETIME) et le moment unique (TIMESTAMP) sans dire à BigQuery où cette heure a été enregistrée.

Scénario problématique :

Si vous ne donnez pas de fuseau horaire, BigQuery va supposer que votre heure locale est l'heure universelle (UTC), ce qui crée un décalage.

sql
-- BigQuery pense que 10h00, c'est l'heure UTC (Universelle).
SELECT CAST(DATETIME '2025-10-07 10:00:00' AS TIMESTAMP) AS date; 
-- BigQuery assume UTC → Décalage horaire garanti
-- Résultat : 2025-10-07 10:00:00 UTC (Potentiellement faux)

La solution :

sql
-- On dit à BigQuery : "Ce 10h00 vient de Paris (UTC+2)".
SELECT TIMESTAMP(DATETIME '2025-10-07 10:00:00', 'Europe/Paris') AS date;
-- Résultat : 2025-10-07 08:00:00 UTC (C'est la vraie heure universelle)

Sans cette précision, BigQuery interprète votre heure locale comme étant déjà en UTC, créant un décalage silencieux qui peut compromettre vos analyses.

2. Requêtes Trop Lentes

BigQuery est très rapide, mais il devient lent si vous lui demandez de calculer quelque chose sur une colonne de date ou d'heure avant de filtrer. Utiliser les dates comme de simples textes (STRING) est aussi très lent.

Le Problème : Calculs Avant Filtrage :

sql
-- BigQuery doit calculer "DATE()" sur CHAQUE ligne avant de savoir s'il doit la garder.
SELECT COUNT(*)
FROM `table_tres_grande`
WHERE DATE(colonne_timestamp) = '2025-10-07'

La solution :

Filtrez directement la colonne de date/heure en utilisant un début et une fin. Cette optimisation ne fonctionne vraiment bien que si votre table est partitionnée par cette colonne. Dans ce cas, BigQuery ne lira que les partitions pertinentes.

sql
-- On filtre directement entre minuit le 7 et minuit le 8. Très rapide.
SELECT COUNT(*)
FROM `table_tres_grande`
WHERE
  colonne_timestamp >= TIMESTAMP '2025-10-07 00:00:00 UTC'
  AND colonne_timestamp < TIMESTAMP '2025-10-08 00:00:00 UTC'

💡

Si votre table n'est pas partitionnée, le filtrage direct aura peu d'effet. BigQuery scannerait quand même la majorité de la table.

Vous devez obligatoirement filtrer sur la colonne de partitionnement elle-même. Si votre table est partitionnée par DATE(colonne_timestamp) mais que vous filtrez sur une autre colonne, BigQuery ne pourra pas éliminer les partitions et scannerait tout de même toute la table.

3. Calculer les Périodes

Calculer une différence en mois peut réserver des surprises. Un écart d'un seul jour entre deux dates peut être comptabilisé comme "1 mois" si ce jour franchit une limite de mois.

Scénario problématique :

sql
-- Un jour d'écart est compté comme 1 mois entier.
SELECT DATE_DIFF(DATE '2025-02-01', DATE '2025-01-31', MONTH) AS Difference_Mois;
-- Résultat : 1

La solution :

Calculez toujours la différence dans l'unité la plus petite (jours ou heures), puis faites votre propre division si besoin.

sql
-- Mesure exacte en jours.
SELECT
  DATE_DIFF(DATE '2025-02-01', DATE '2025-01-31', DAY) AS Difference_Jours;
-- Résultat : 1

4. FORMAT_TIMESTAMP dans les Jointures

Convertir des timestamps en texte pour faire des jointures force BigQuery à comparer du texte au lieu de nombres, ce qui est beaucoup plus lent et empêche toute optimisation.

Scénario problématique :

sql
-- BigQuery doit convertir TOUTES les lignes des deux tables en texte avant de joindre
SELECT a.*, b.*
FROM table_a a
JOIN table_b b
  ON FORMAT_TIMESTAMP('%Y-%m-%d', a.date_event) = FORMAT_TIMESTAMP('%Y-%m-%d', b.date_transaction);

Optimisation :

Utilisez la fonction TIMESTAMP_TRUNC() pour aligner la date sans jamais quitter le type TIMESTAMP :

sql
-- L'optimisation : on tronque au Jour (DAY)
SELECT a.*, b.*
FROM table_a a
JOIN table_b b
  ON TIMESTAMP_TRUNC(a.date_event, DAY) = TIMESTAMP_TRUNC(b.date_transaction, DAY);

💡 L'intérêt avec l'exemple Visite/Commande

Cette technique est essentielle pour lier des événements qui ne se sont pas produits à la même microseconde (ce qui est le cas de 99 % de vos données).

L'échec de l'égalité directe : Il est impossible que la date_visite et la date_commande aient le même TIMESTAMP exact. Une jointure directe échoue.

L'Alignement : TIMESTAMP_TRUNC(..., DAY) ramène les deux événements à minuit du même jour, permettant la jointure

5. Changements d'Heure

Quand les horloges locales avancent (heure d'été) ou reculent (heure d'hiver), l'heure de 2h00 du matin, par exemple, peut soit ne pas exister du tout, soit exister deux fois. Si vous utilisez le type DATETIME pour planifier ou analyser des événements précis (comme des tâches qui se déclenchent à une heure fixe), vous risquez de rater l'événement ou de le compter en double.

Scénario problématique :

Si vous comptez sur une colonne DATETIME pour savoir quand une tâche s'est déclenchée à 2h30 du matin, le jour du changement d'heure :

En passant à l'heure d'été, l'heure 2h30 n'existe pas.

En passant à l'heure d'hiver, l'heure 2h30 se produit deux fois.

Optimisation :

Utilisez toujours le type TIMESTAMP pour les événements récurrents. Comme le TIMESTAMP est basé sur l'heure universelle (UTC), il n'est jamais affecté par les changements d'heure locaux.

sql
-- Si vous devez planifier, comparez toujours le moment universel (TIMESTAMP).
-- Ceci garantit que la durée entre les deux est toujours de 24 heures exactes.
SELECT 
  TIMESTAMP '2025-03-30 02:30:00+02' AS Heure_Locale_Avant_Changement,
  TIMESTAMP '2025-03-30 02:30:00+01' AS Heure_Locale_Apres_Changement;
  
-- BigQuery gère automatiquement le saut pour vous. Si vous utilisez TIMESTAMP, 
-- vous savez qu'il s'agit d'un moment unique.

Les Outils Utiles

Ces fonctions vous aideront à faire le tri et le calcul de vos données efficacement :

Fonction	Utilité	Exemple
`TIMESTAMP_TRUNC()`	Permet de couper un moment à une unité (début du mois, de l'année, de la semaine). Idéal pour regrouper des données.	`TIMESTAMP_TRUNC(mon_timestamp, MONTH)`
`DATE_TRUNC()`	Idem que la fonction ci-dessus, mais pour le type `DATE`.	`DATE_TRUNC(ma_date, WEEK)`
`TIMESTAMP_DIFF()`	Calcule la différence entre deux moments exacts.	`TIMESTAMP_DIFF(ts1, ts2, HOUR)`
`FORMAT_DATETIME()`	Sert à changer l'apparence d'une date ou d'une heure.	`FORMAT_DATETIME("%Y-%m-%d", ma_date)`