Comment grouper par semaine dans PostgreSQL

Vous avez besoin d'analyser des données par semaine dans PostgreSQL ? Voici comment regrouper vos enregistrements à l'aide des fonctions de date intégrées pour obtenir des informations claires et temporelles.

Le regroupement des données par semaine est une exigence courante dans les rapports. Il est souvent utilisé dans les rapports d'entreprise pour suivre des indicateurs tels que les revenus hebdomadaires, les inscriptions d'utilisateurs ou le trafic d'un site Web. Le regroupement par semaine permet d'atténuer les fluctuations quotidiennes et d'obtenir une vision plus claire des tendances au fil du temps. Il est particulièrement utile pour comparer les performances sur des intervalles de temps cohérents, comme la croissance d'une semaine sur l'autre ou l'analyse d'une cohorte hebdomadaire.

PostgreSQL offre des outils puissants pour travailler avec les dates, mais le regroupement par semaine - en particulier lorsque vous avez besoin d'un jour de début ou d'un fuseau horaire spécifique - peut être délicat si vous n'êtes pas familier avec les bonnes fonctions.

Cet article vous explique comment grouper par semaine dans PostgreSQL en utilisant date_trunc() et date_bin(), comment ajuster les fuseaux horaires, et comment définir un jour de début de semaine personnalisé comme mercredi ou jeudi.

Vous souhaitez acquérir des compétences solides et pratiques sur PostgreSQL depuis le début ? Essayez la formationSQL de A à Z dans PostgreSQL sur LearnSQL.fr. Il est conçu pour vous faire passer du statut de débutant à celui d'utilisateur SQL confirmé, grâce à des exercices concrets et à des conseils étape par étape.

Grouper par semaine avec date_trunc()

La fonction date_trunc() est la manière la plus courante de grouper les données par semaine dans PostgreSQL. Elle tronque un timestamp ou une date au début d'une unité de temps spécifiée, comme un jour, une semaine ou un mois.

Utilisation de base :

date_trunc('week', some_date)

Cette fonction retournera une nouvelle date représentant le début de la semaine ISO, qui est le lundi à 00:00:00.

Exemple :

Supposons que vous disposiez d'une colonne created_at et que vous souhaitiez compter les inscriptions par semaine :

SELECT
  date_trunc('week', created_at) AS week_start,
  COUNT(*) AS signups
FROM users
GROUP BY week_start
ORDER BY week_start;

Cette requête regroupe les utilisateurs en fonction de la semaine où ils se sont inscrits, chaque semaine commençant le lundi.

Voici à quoi pourrait ressembler le résultat de cette requête :

Notez que le résultat de date_trunc('week', some_date) comprend à la fois la date et l'heure, l'heure étant fixée à 00:00:00 (minuit).

Regroupement par semaine dans un fuseau horaire spécifique

Lorsque l'on travaille avec des horodatages, en particulier dans des applications globales, il est essentiel de connaître le fuseau horaire. L'activité d'un utilisateur peut se dérouler le dimanche soir dans un fuseau horaire, mais apparaître le lundi matin en UTC. Cela peut affecter la façon dont les données sont regroupées en semaines, ce qui peut conduire à des rapports incorrects si les fuseaux horaires sont ignorés.

Par défaut, PostgreSQL regroupe les horodatages en fonction du fuseau horaire de la session, qui est généralement défini lors de l'établissement de la connexion. Cela signifie que date_trunc('week', some_timestamptz_column) utilisera le fuseau horaire actuel de la session. Si vous ne définissez pas explicitement le fuseau horaire, PostgreSQL utilisera la valeur par défaut de la configuration de votre serveur ou de votre client.

Pour assurer la cohérence, en particulier dans les rapports, vous pouvez utiliser la clause AT TIME ZONE pour convertir les horodatages dans un fuseau horaire spécifique avant de les tronquer.

Exemple d'utilisation :

SELECT
  date_trunc('week', created_at AT TIME ZONE 'UTC') AS week_start_utc,
  COUNT(*) AS signups
FROM users
GROUP BY week_start_utc
ORDER BY week_start_utc;

Cette requête tronque chaque horodatage created_at au début de la semaine en UTC, ce qui garantit la cohérence des regroupements hebdomadaires entre les fuseaux horaires.

Si vous travaillez avec une colonne timestamptz et que vous souhaitez regrouper les données en fonction d'une heure locale spécifique, utilisez la fonction de troncature :

date_trunc('week', created_at AT TIME ZONE 'America/New_York')

Ceci aligne vos semaines sur le début du lundi à l'heure locale de New York.

Jours de début de semaine personnalisés (par exemple, mercredi ou jeudi)

La fonction date_trunc('week', some_date) de PostgreSQL aligne toujours les semaines sur le lundi, en suivant la définition de la semaine ISO. Ceci est fixe et ne peut pas être modifié directement par date_trunc().

Si votre rapport exige que les semaines commencent un jour différent, comme le mercredi ou le jeudi, vous devrez appliquer une solution de contournement simple : décaler la date avant la troncature, puis la recaler après.

Logique de contournement

Pour que la semaine commence un jour spécifique, soustrayez le nombre approprié de jours avant la troncature, puis ajoutez-le :

-- Week starting on Wednesday
date_trunc('week', some_date - interval '2 days') + interval '2 days'

Le calendrier est ainsi décalé de deux jours (le lundi devient le samedi), la semaine est tronquée jusqu'au début de la semaine ajustée (samedi), puis elle est à nouveau décalée jusqu'au mercredi.

Exemples de jours de début de semaine différents

Utilisez ce modèle dans vos clauses SELECT, GROUP BY ou ORDER BY pour aligner les données sur la structure hebdomadaire de votre choix.

Regroupement flexible des dates et heures avec date_bin() (PostgreSQL 14+)

PostgreSQL 14 a introduit une nouvelle fonction puissante : date_bin(). Elle vous donne plus de contrôle que date_trunc() lors du regroupement par heure, particulièrement si vous voulez que les semaines commencent à une date spécifique ou à un jour de la semaine personnalisé.

date_bin() La fonction date_bin() vous permet de regrouper les horodatages en périodes égales (semaines, jours ou heures) à partir de la date et de l'heure de votre choix. Ce point de départ est appelé l'ancre.

L'ancre définit le moment où le premier groupe commence. PostgreSQL construit ensuite les autres groupes en se basant sur ce point, à la fois vers l'avant et vers l'arrière dans le temps. Ainsi, si votre ancre est le 1er janvier 2025 et que vous groupez par 7 jours, PostgreSQL créera des intervalles d'une semaine à partir de cette date, ainsi que des intervalles antérieurs qui correspondent au même modèle.

Syntaxe

date_bin(interval, timestamp, anchor)

• interval: la longueur de chaque groupe (par exemple '7 days')
• timestamp: la colonne que vous regroupez (par exemple created_at)
• anchor: la date et l'heure exactes auxquelles le regroupement doit s'aligner

Exemple : Regroupement par tranches d'une semaine à partir du 1er janvier

SELECT
  date_bin('7 days', created_at, TIMESTAMP '2025-01-01') AS custom_week,
  COUNT(*) AS signups
FROM users
GROUP BY custom_week
ORDER BY custom_week;

Cet exemple regroupe les utilisateurs par intervalles de 7 jours à partir du 1er janvier 2025, quel que soit le jour de la semaine. Vous pouvez utiliser n'importe quelle date d'ancrage, par exemple le début de votre année fiscale ou le jour du lancement de votre produit.

Utilisez date_bin() si vous souhaitez que vos semaines commencent à un jour personnalisé ou à une date précise, si vous avez besoin de plages de temps qui ne sont pas basées sur des unités de calendrier (par exemple, tous les 10 jours ou toutes les 6 heures), ou si vous souhaitez avoir un contrôle total sur la manière dont vos données temporelles sont regroupées.

Si vous utilisez PostgreSQL 14 ou une version plus récente, date_bin() est une alternative flexible qui peut rendre vos rapports hebdomadaires plus précis et mieux adaptés à vos besoins spécifiques.

Conclusion

Le regroupement par semaine dans PostgreSQL peut être simple ou hautement personnalisable, selon vos besoins. Vous pouvez utiliser date_trunc() pour les semaines ISO standard (commençant le lundi) ou appliquer de petits ajustements pour commencer les semaines le jour de votre choix. Pour encore plus de flexibilité - comme la définition d'une date d'ancrage spécifique ou l'utilisation d'intervalles de temps non standard -date_bin() (disponible à partir de PostgreSQL 14) est le meilleur outil pour ce travail.

Comprendre comment travailler avec les dates, les fuseaux horaires et la logique de regroupement est essentiel pour toute personne qui fait de l'analyse de données ou du reporting avec PostgreSQL.

Si vous êtes prêt à aiguiser vos compétences en PostgreSQL grâce à une pratique réelle, consultez la section SQL de A à Z dans PostgreSQL sur LearnSQL.fr. Il s'agit d'un parcours d'apprentissage pratique, étape par étape, qui vous aidera à passer des bases à des techniques plus avancées telles que celles abordées dans cet article.