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:
- Each week begins on Monday.
- 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,
PostgreSQL Extract: 9.9. Date/Time Functions and Operators2005-01-01
is part of the 53rd week of year 2004, and2006-01-01
is part of the 52nd week of year 2005, while2012-12-31
is part of the first week of 2013. It’s recommended to use theisoyear
field together withweek
to get consistent results.
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.
Leave a Reply