Often in PostgreSQL you may want to get the week number from a date.
You can use the following syntax to do so:
SELECT *,
to_char(date_sold, 'IYYY-IW') AS week_num
FROM products;
This particular example will extract the week number from the date in the date_sold column and save the result to a new column named week_num.
For example, if the date in the date_sold column is 2024-04-01 then this syntax would return 2024-14 as the result in the week_num column.
Note: IW Returns the week number and IYYY returns the corresponding year.
The following example shows how to use this syntax in practice.
Example: How to Get Week Number from Date in PostgreSQL
Suppose that we create the following table named products that contains information about when various products were sold at some retail store:
-- create table named 'products'
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
date_sold DATE
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A0045BH', '2024-04-01');
INSERT INTO products VALUES (0002, 'A00933R', '2024-06-02');
INSERT INTO products VALUES (0003, 'BRRHHYL', '2024-07-03');
INSERT INTO products VALUES (0004, 'BR33503', '2024-09-04');
INSERT INTO products VALUES (0005, 'CHYYGG4', '2024-10-05');
INSERT INTO products VALUES (0006, 'C743559', '2024-11-06');
INSERT INTO products VALUES (0007, 'HTT56HH', '2024-12-07');
-- view all rows in table
SELECT * FROM products;
id | name | date_sold
----+---------+------------
1 | A0045BH | 2024-04-01
2 | A00933R | 2024-06-02
3 | BRRHHYL | 2024-07-03
4 | BR33503 | 2024-09-04
5 | CHYYGG4 | 2024-10-05
6 | C743559 | 2024-11-06
7 | HTT56HH | 2024-12-07
Suppose that we would like to get the week number from each date in the date_sold column.
We can use the following syntax to do so:
SELECT *,
to_char(date_sold, 'IYYY-IW') AS week_num
FROM products;
id | name | date_sold | week_num
----+---------+------------+----------
1 | A0045BH | 2024-04-01 | 2024-14
2 | A00933R | 2024-06-02 | 2024-22
3 | BRRHHYL | 2024-07-03 | 2024-27
4 | BR33503 | 2024-09-04 | 2024-36
5 | CHYYGG4 | 2024-10-05 | 2024-40
6 | C743559 | 2024-11-06 | 2024-45
7 | HTT56HH | 2024-12-07 | 2024-49
Notice that the new column named week_num contains the year and week number for the corresponding date in the date_sold column.
You can use similar syntax to get the week number for dates in a column of your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Day of the Week from Date
PostgreSQL: How to Get the First Day of the Week
PostgreSQL: How to Select Only Weekends
