PostgreSQL: How to Extract Day of the Week from Date

Often in PostgreSQL you may want to extract the day of the week from a date field.

You can use the following methods to do so:

Method 1: Extract Day of Week as Number (1=Monday, 7=Sunday)

SELECT *,
       date_part('isodow', date_sold) AS day_number
FROM products;

This particular example will extract the day of the week from the date_sold column as a number between 1 (Monday) and 7 (Sunday) and save the results to a new column named day_number.

Method 2: Extract Day of Week as Name (e.g. “Monday”)

SELECT *,
       to_char(date_sold, 'Day') AS day_name
FROM products;

This particular example will extract the day of the week from the date_sold column as a name (e.g. “Monday”) and save the results to a new column named day_name.

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

Example: How to Extract Day of Week 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-09-01');
INSERT INTO products VALUES (0002, 'A00933R', '2024-09-02');
INSERT INTO products VALUES (0003, 'BRRHHYL', '2024-09-03');
INSERT INTO products VALUES (0004, 'BR33503', '2024-09-04');
INSERT INTO products VALUES (0005, 'CHYYGG4', '2024-09-05');
INSERT INTO products VALUES (0006, 'C743559', '2024-09-06');
INSERT INTO products VALUES (0007, 'HTT56HH', '2024-09-07');

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

 id |  name   | date_sold  
----+---------+------------
  1 | A0045BH | 2024-09-01
  2 | A00933R | 2024-09-02
  3 | BRRHHYL | 2024-09-03
  4 | BR33503 | 2024-09-04
  5 | CHYYGG4 | 2024-09-05
  6 | C743559 | 2024-09-06
  7 | HTT56HH | 2024-09-07

Suppose that we would like to extract the day of the week from each date in the date_sold column.

We can use the following syntax to do so:

SELECT *,
       date_part('isodow', date_sold) AS day_number,
       to_char(date_sold, 'Day') AS day_name
FROM products;

 id |  name   | date_sold  | day_number | day_name  
----+---------+------------+------------+-----------
  1 | A0045BH | 2024-09-01 |          7 | Sunday   
  2 | A00933R | 2024-09-02 |          1 | Monday   
  3 | BRRHHYL | 2024-09-03 |          2 | Tuesday  
  4 | BR33503 | 2024-09-04 |          3 | Wednesday
  5 | CHYYGG4 | 2024-09-05 |          4 | Thursday 
  6 | C743559 | 2024-09-06 |          5 | Friday   
  7 | HTT56HH | 2024-09-07 |          6 | Saturday 

Notice that two new columns have been added to the table:

• day_number contains the day of the week as a number.
• day_name contains the day of the week as a name.

You can use similar syntax to extract the day of the week from a date column in your own table.

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 Select the Last Row per Group
PostgreSQL: How to Calculate Difference Between Rows