How to Group by Week in PostgreSQL

Need to analyze data by week in PostgreSQL? Here's how to group your records using built-in date functions for clear, time-based insights.

Grouping data by week is a common requirement in reporting. It’s often used in business reporting to track metrics like weekly revenue, user signups, or website traffic. Grouping by week helps smooth out daily fluctuations and gives you a clearer view of trends over time. It's especially helpful when comparing performance across consistent time intervals, like "Week over Week" growth or weekly cohort analysis.

PostgreSQL offers powerful tools for working with dates, but grouping by week—especially when you need a specific start day or time zone—can be tricky if you’re not familiar with the right functions.

This article walks you through how to group by week in PostgreSQL using date_trunc() and date_bin(), how to adjust for time zones, and how to set a custom week start day like Wednesday or Thursday.

Want to build strong, practical PostgreSQL skills from the ground up? Try the SQL From A to Z in PostgreSQL track on LearnSQL.com. It’s designed to take you from beginner to confident SQL user with real-world exercises and step-by-step guidance.

Grouping by Week with date_trunc()

The date_trunc() function is the most common way to group data by week in PostgreSQL. It truncates a timestamp or date to the start of a specified time unit—like day, week, or month.

Basic usage:

date_trunc('week', some_date)

This will return a new date representing the start of the ISO week, which is Monday at 00:00:00.

Example:

Let’s say you have a created_at column and want to count signups per week:

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

This query groups users by the week they signed up, with each week starting on Monday.

Here’s how the output of that query might look:

Note that the result of date_trunc('week', some_date) includes both the date and time, with the time set to 00:00:00 (midnight).

Grouping by Week in a Specific Time Zone

When working with timestamps, especially in global applications, time zone awareness is essential. A user's activity might happen on Sunday night in one time zone but appear as Monday morning in UTC. This can affect how data is grouped into weeks—leading to incorrect reporting if time zones are ignored.

By default, PostgreSQL groups timestamps based on the session time zone, which is usually set when the connection is established. This means that date_trunc('week', some_timestamptz_column) will use the current time zone setting for the session. If you're not explicitly setting the time zone, PostgreSQL will use the default from your server or client configuration.

To ensure consistency—especially in reports—you can use the AT TIME ZONE clause to convert timestamps to a specific time zone before truncating.

Example usage:

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;

This query truncates each created_at timestamp to the beginning of the week in UTC, ensuring that weekly groupings are consistent across time zones.

If you're working with a timestamptz column and want to group by a specific local time, use:

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

This aligns your weeks to the start of Monday in New York local time.

Custom Week Start Days (e.g., Wednesday or Thursday)

The date_trunc('week', some_date) function in PostgreSQL always aligns weeks to start on Monday, following the ISO week definition. This is fixed and can't be changed directly through date_trunc().

If your reporting requires weeks to start on a different day—like Wednesday or Thursday—you'll need to apply a simple workaround: shift the date before truncation, then shift it back after.

Workaround Logic

To make the week start on a specific day, subtract the appropriate number of days before truncation and then add it back:

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

This shifts the calendar back by 2 days (so Monday becomes Saturday), truncates to the start of that adjusted week (Saturday), then shifts forward again to Wednesday.

Examples for Different Week Start Days

Use this pattern inside your SELECT, GROUP BY, or ORDER BY clauses to align data to your preferred weekly structure.

Flexible date and time grouping with date_bin() (PostgreSQL 14+)

PostgreSQL 14 introduced a powerful new function: date_bin(). It gives you more control than date_trunc() when grouping by time, especially if you want weeks to start on a specific date or a custom day of the week.

date_bin() lets you group timestamps into equal time periods—like weeks, days, or hours—starting from any date and time you choose. This starting point is called the anchor.

The anchor defines when the first group begins. PostgreSQL then builds the other groups based on that—both forward and backward in time. So if your anchor is January 1, 2025, and you group by 7 days, PostgreSQL will create week-long intervals starting from that date, and also earlier intervals that fit the same pattern.

Syntax

date_bin(interval, timestamp, anchor)

• interval: how long each group should be (e.g. '7 days')
• timestamp: the column you're grouping (e.g. created_at)
• anchor: the exact date and time your grouping should align to

Example: Grouping by 1-Week Bins Starting on January 1

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;

This groups users into 7-day intervals starting on January 1, 2025, regardless of what day of the week that is. You can use any anchor date you like—for example, the start of your fiscal year or the day your product launched.

Use date_bin() if you want your weeks to start on a custom day or exact date, need time ranges that aren’t based on calendar units (e.g. every 10 days or 6 hours), or want full control over how your time-based data is grouped.

If you're using PostgreSQL 14 or newer, date_bin() is a flexible alternative that can make your weekly reports more accurate and aligned with your specific needs.

Conclusion

Grouping by week in PostgreSQL can be simple or highly customizable, depending on your needs. You can use date_trunc() for standard ISO weeks (starting on Monday) or apply small adjustments to start weeks on any day you like. For even more flexibility—like setting a specific anchor date or using non-standard time intervals—date_bin() (available in PostgreSQL 14 and up) is the best tool for the job.

Understanding how to work with dates, time zones, and grouping logic is essential for anyone doing serious data analysis or reporting in PostgreSQL.

If you're ready to sharpen your PostgreSQL skills with real-world practice, check out the SQL From A to Z in PostgreSQL track on LearnSQL.com. It’s a hands-on, step-by-step learning path that will help you go from the basics to more advanced techniques like the ones covered in this article.