SQL

Extracting Day of Week from Date

Often times when working with time series data for analysis, you may be required to extract information from dates such a the quarter, month, year or even day of the week. For example, analyzing weekend sales may be an interesting exercise to understand business performance behavior. This short snippet demonstrates how to extract such components from date.

enrollments table

For this exercise, I will use the enrollments table which looks like below

SELECT * FROM enrollments
enrollment_id | employee_id | session_id | enrollment_date | completion_status | score --------------+-------------+------------+-----------------+-------------------+------- 1 | 1 | 101 | 2023-01-20 | Completed | 88 2 | 3 | 101 | 2023-01-25 | Completed | 95 3 | 2 | 102 | 2023-02-15 | In Progress | 4 | 7 | 102 | 2023-02-20 | Completed | 76 5 | 1 | 103 | 2023-12-15 | Dropped | 6 | 4 | 103 | 2023-12-20 | Completed | 82 7 | 6 | 104 | 2023-03-10 | Completed | 91 8 | 2 | 105 | 2023-01-30 | Completed | 79 9 | 7 | 105 | 2023-02-10 | In Progress | (9 rows)

ISODOW

The ISODOW maps the day of week into the date. It returns 1-7 where 1 corresponds to Monday and 7 to Sunday. To be clear, the mapping is given below

Monday    --> 1
Tuesday   --> 2
Wednesday --> 3
Thursday  --> 4
Friday    --> 5
Saturday  --> 6
Sunday    --> 7

For example:

SELECT EXTRACT(isodow from '2025-04-01'::date);
extract --------- 2 (1 row)

Extract Day of Week from enrollments

Let's put that together in a query on enrollments table.

SELECT enrollment_date, 
        EXTRACT(isodow from enrollment_date) AS day_of_week,
        EXTRACT(month from enrollment_date) AS month, 
        completion_status 
FROM enrollments;
enrollment_date | day_of_week | month | completion_status ----------------+-------------+-------+------------------- 2023-01-20 | 5 | 1 | Completed 2023-01-25 | 3 | 1 | Completed 2023-02-15 | 3 | 2 | In Progress 2023-02-20 | 1 | 2 | Completed 2023-12-15 | 5 | 12 | Dropped 2023-12-20 | 3 | 12 | Completed 2023-03-10 | 5 | 3 | Completed 2023-01-30 | 1 | 1 | Completed 2023-02-10 | 5 | 2 | In Progress (9 rows)