Often in PostgreSQL you may want to get a week start date from a week number.
You can use the following syntax with the to_date() function to do so:
SELECT *,
to_date(sales_week, 'iyyyiw')
FROM sales;
This particular example will return the week start date from each week number in the sales_week column.
For example, if the sales_week column contains the value 202450 then the to_date() function would return 2024-12-09 since this is the week start date of week 50 in the year 2024.
The following example shows how to use each of these methods in practice.
Example: How to Get Week Start Date from Week Number in PostgreSQL
Suppose that we create the following table named sales that contains information about the total sales made during various weeks of the year at some company:
-- create table named 'sales'
CREATE TABLE sales (
sales_week TEXT,
amount INT
);
-- add rows to the table
INSERT INTO sales VALUES ('202415', 54);
INSERT INTO sales VALUES ('202420', 70);
INSERT INTO sales VALUES ('202428', 77);
INSERT INTO sales VALUES ('202436', 71);
INSERT INTO sales VALUES ('202446', 90);
INSERT INTO sales VALUES ('202450', 86);
-- view all rows in table
SELECT * FROM sales;
sales_week | amount
------------+--------
202415 | 54
202420 | 70
202428 | 77
202436 | 71
202446 | 90
202450 | 86
Suppose that we would like to return the week start date for each week number in the sales_week column.
We can use the following syntax to do so:
SELECT *,
to_date(sales_week, 'iyyyiw')
FROM sales;
sales_week | amount | to_date
------------+--------+------------
202415 | 54 | 2024-04-08
202420 | 70 | 2024-05-13
202428 | 77 | 2024-07-08
202436 | 71 | 2024-09-02
202446 | 90 | 2024-11-11
202450 | 86 | 2024-12-09
The resulting table contains a new column that displays the week start date for each week number in the sales_week column.
You can use similar syntax to get the week start date from a week number in your own table in PostgreSQL.
Note: You can find the complete documentation for the to_date() function in PostgresQL here.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Group Results by Week
PostgreSQL: How to Get the First Day of the Week
PostgreSQL: Calculate Difference Between Dates in Weeks
