Often in PostgreSQL you may want to get the first day of the week.
You can use the following basic syntax to do so:
SELECT date_trunc('week', start_date)::date AS first_day
FROM emp;
This particular example will return the first day of the week for each date in the start_date column of the table named emp.
Here is how this syntax works:
- We use the date_trunc() function to truncate the date to the week, which returns the first day of the week by default.
- Then, we cast the result to a date.
- Lastly, we save the result to a column named first_day.
It’s worth noting that the date_trunc() function assumes the “first” day of the week is Monday.
If you would instead like to use Sunday as the first day of the week, then you can use the following syntax:
SELECT date_trunc('week', start_date)::date - interval '1 day' AS first_day
FROM emp;
The following example shows how to use this syntax in practice.
Example: How to Get First Day of the Week in PostgreSQL
Suppose that we create the following table named emp that contains information about various employees at some company including their starting date and their total sales made:
-- create table named 'emp'
CREATE TABLE emp (
empID INT PRIMARY KEY,
start_date DATE,
sales INT
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '2021-04-01', 134);
INSERT INTO emp VALUES (0002, '2022-07-01', 201);
INSERT INTO emp VALUES (0003, '2022-08-15', 155);
INSERT INTO emp VALUES (0004, '2022-09-01', 98);
INSERT INTO emp VALUES (0005, '2023-11-23', 65);
INSERT INTO emp VALUES (0006, '2024-02-20', 52);
INSERT INTO emp VALUES (0007, '2024-05-15', 50);
-- view all rows in table
SELECT * FROM emp;
empid | start_date | sales
-------+------------+-------
1 | 2021-04-01 | 134
2 | 2022-07-01 | 201
3 | 2022-08-15 | 155
4 | 2022-09-01 | 98
5 | 2023-11-23 | 65
6 | 2024-02-20 | 52
7 | 2024-05-15 | 50
Suppose that we would like to get the first day of the week for each date in the start_date column.
We can use the following syntax to do so:
SELECT empID,
start_date,
date_trunc('week', start_date)::date AS first_day
FROM emp;
empid | start_date | first_day
-------+------------+------------
1 | 2021-04-01 | 2021-03-29
2 | 2022-07-01 | 2022-06-27
3 | 2022-08-15 | 2022-08-15
4 | 2022-09-01 | 2022-08-29
5 | 2023-11-23 | 2023-11-20
6 | 2024-02-20 | 2024-02-19
7 | 2024-05-15 | 2024-05-13
The new column named first_day contains the first day of the week for each corresponding date in the start_date column.
You can use similar syntax to get the first day of the week for a date column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Get the Last Day of the Month
PostgreSQL: How to Extract Month from Date
PostgreSQL: How to Extract Year and Month from Date
PostgreSQL: How to Calculate Difference Between Dates in Months
