PostgreSQL: How to Select Only Weekends

Often in PostgreSQL you may want to only select rows where a date field occurs on a weekend.

You can use the following syntax with the isodow function to do so:

SELECT * 
FROM emp
WHERE EXTRACT(ISODOW FROM start_date) IN (6, 7);

This particular example will select all rows from the emp table where the date in the start_date column is on a weekend.

Note that the isodow function returns the day of the week as a number where:

• 1 = Monday
• 2 = Tuesday
• 3 = Wednesday
• 4 = Thursday
• 5 = Friday
• 6 = Saturday
• 7 = Sunday

By filtering for the row where the number is 6 or 7, we are able to return only the rows where the date is on a weekend.

The following example shows how to use each of these methods in practice.

Example: How to Select Only Weekends in PostgreSQL

Suppose that we create the following table named emp that contains information about when various employees started working at some company:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT,
  start_date DATE,
  sales INT
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2024-04-01', 134);
INSERT INTO emp VALUES (0002, '2024-07-01', 201);
INSERT INTO emp VALUES (0003, '2024-08-15', 155);
INSERT INTO emp VALUES (0004, '2024-09-01', 98);
INSERT INTO emp VALUES (0005, '2024-11-23', 65);
INSERT INTO emp VALUES (0006, '2024-12-14', 52);
INSERT INTO emp VALUES (0007, '2024-12-15', 50);

-- view all rows in table
SELECT * FROM emp;

 empid | start_date | sales 
-------+------------+-------
     1 | 2024-04-01 |   134
     2 | 2024-07-01 |   201
     3 | 2024-08-15 |   155
     4 | 2024-09-01 |    98
     5 | 2024-11-23 |    65
     6 | 2024-12-14 |    52
     7 | 2024-12-15 |    50

Suppose that we would like to only select the rows where the date in the start_date column is on a weekend.

We can use the following syntax to do so:

SELECT * 
FROM emp
WHERE EXTRACT(ISODOW FROM start_date) IN (6, 7);

 empid | start_date | sales 
-------+------------+-------
     4 | 2024-09-01 |    98
     5 | 2024-11-23 |    65
     6 | 2024-12-14 |    52
     7 | 2024-12-15 |    50

The resulting table only contains the rows where the date in the start_date column is on a weekend.

For example, we can verify:

• 2024-09-01 is on a Sunday.
• 2024-11-23 is on a Saturday.

And so on.

You can use similar syntax to select only rows that occur on weekends in your own table.

Note: You can find the complete documentation for the isodow 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