Extract day of week from date field in PostgreSQL assuming weeks start on Monday

Adjusting weekdays to your needs

If your days of the week should start from 0 (Monday), you got it covered:

SELECT EXTRACT(ISODOW FROM date_column) - 1 AS day_of_week 
FROM your_table;


Here ISODOW returns weekday numbers as 1 for Monday through 7 for Sunday. Subtracting 1 gives you a nice range of 0-6 starting with Monday.

Textual representation of weekdays

If you'd rather write out the weekdays, PostgreSQL provides the TO_CHAR() function:

SELECT TO_CHAR(date_column, 'Day') AS day_text 
FROM your_table;


There are a bunch of formatting tokens, for example 'Day' for full name, 'Dy' for three letters, or simply 'D' for one letter.

Making weekdays dance to your tune

For custom weekday numbering, you can use some sweet CASE statements:

SELECT CASE 
  WHEN EXTRACT(DOW FROM date_column)::int = 0 THEN 6
  ELSE EXTRACT(DOW FROM date_column)::int - 1 
END AS custom_day_of_week
FROM your_table;


This snippet will make Sunday dance as 6 and Monday groove as 0. Groovy, right?

Beyond simple extraction: More tricks up your sleeve

Days of the week for a range of dates

If you want to build a weekday table over a range of dates:

-- I'm sure it's nice to know what day of the week your birthday was in 2023

WITH date_series AS (
  SELECT generate_series(
    '2023-01-01'::date, 
    '2023-01-07', 
    '1 day'::interval
  )::date as series_date
)
SELECT series_date, EXTRACT(ISODOW FROM series_date) - 1 AS weekday_number
FROM date_series;


Filtering weekdays for business analysis

For a working week report, filtering for weekdays is as easy as pie:

-- Sorry, weekend! You're not invited to this party.

SELECT *
FROM your_table
WHERE EXTRACT(ISODOW FROM date_column) BETWEEN 1 AND 5;


Timezones, the spoilsports

For accurate extraction from dates, mind the timezones:

-- UTC? Or not UTC? That's the question.
SELECT EXTRACT(ISODOW FROM date_column AT TIME ZONE 'UTC') - 1 AS day_of_week 
FROM your_table;


Be careful! Timezones can alter the perceived day of a datetime significantly.