How to Find the Week Number in PostgreSQL, MySQL, and SQL Server

Problem

You need to figure out the week number from date, assuming Monday is the first day of the week using PostgreSQL, MySQL, or SQL Server.

Background

To work through this problem, we’ll use orders for smelt pizza.  For each order date we’ll calculate the week number in PostgreSQL. Our example is based on the sample PizzaDB, you can get it here.

Here is the query we’ll start with that work with MySQL, PostgreSQL, and SQL Server:

select o.OrderDate, p.ProductName, i.Quantity, p.Price
from CustomerOrder o
    inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID
    inner join Product p on i.ProductID = p.ProductID
where p.ProductName = 'Smelt Pizza'
order by OrderDate

and the corresponding results:

Note: I’ve never had Smelt Pizza, and never plan to… ?.

Solution

We’ll walk through several solutions to calculate the week number.  Keep in mind the week number starts at 1 and, depending on how the calendar laysout, go past 52.

Solution 1 – Use EXTRACT() to Calculate Week Number

This solution works with MySQL and PostgreSQL and is a SQL standard.  Given this, I prefer it over other solutions for these two DBMS’s.

Here is the specific SQL for to calculate the week number in PostgreSQL:

select extract('week' from o.OrderDate) OrderWeek,
    o.OrderDate, p.ProductName, i.Quantity, p.Price
from CustomerOrder o
    inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID
    inner join Product p on i.ProductID = p.ProductID
where p.ProductName = 'Smelt Pizza'
order by OrderDate

When using extract you specify the interval, there are many to choose from.  Since we’re working with weeks, then the week interval works well.

For MySQL, use the same SQL query to find the week number, but instead of enclosing the data interval within quotes, specify it without them.

select extract(week from o.OrderDate) OrderWeek,
    o.OrderDate, p.ProductName, i.Quantity, p.Price
from CustomerOrder o
    inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID
    inner join Product p on i.ProductID = p.ProductID
where p.ProductName = 'Smelt Pizza'
order by OrderDate

Notice the subtle difference, which I bolded for you to see it better.

Solution 2 – Use DATEPART()

This Solution works with SQL Server.  Rather than use “week” you can use “iso_week” to get the week number. 

select datepart(iso_week,o.OrderDate) OrderWeek,
    o.OrderDate, p.ProductName, i.Quantity, p.Price
from CustomerOrder o
    inner join CustomerOrderItem i on o.CustomerOrderID = i.CustomerOrderID
    inner join Product p on i.ProductID = p.ProductID
where p.ProductName = 'Smelt Pizza'
order by OrderDate

Discussion

Weeks are seemingly simple but the details are confounding.  For some reason we humans love to complicate matters.  As such, there are numerous ways to “count” weeks and return a week number.

For this recipe we settled on the ISO 8601 week numbering standard  If you’re not familiar with the standard here are some items to consider:

1. Each week begins on Monday.
2. The first week of a year contains January 4 of that year.

I think the PostgreSQL Extract documentation gives the most succinct explanation:

In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. It’s recommended to use the isoyear field together with week to get consistent results.

PostgreSQL Extract: 9.9. Date/Time Functions and Operators

Keep in mind there are other ways to count weeks.  So when working with users and other business analysts, be sure you’re all on the same page as to which one to use.

I seen cases for both, and have built “time” based dimension containing both ISO-8601 and traditional week numbers.

Other Interesting Articles

• SQL SUBSTRING Function (Transact SQL)
• Replace using PostgreSQL
• Database Design In Four Steps – Build the Sample PizzaDB
• SQL Data Conversions
• Avoid the 5 Most Common SQL Mistakes Beginners Make