SQL Extract Day of Week

Returns which day of the week a date fell on — 0 for Sunday through 6 for Saturday in Postgres.

What & Why

EXTRACT(DOW FROM date_column) returns the day of the week as a number: 0 = Sunday, 1 = Monday, up through 6 = Saturday. Useful for questions like "do more signups happen on weekends."

See How It Works

BUSINESS QUESTION

Product wants feedback volume by weekday number.

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

EXAMPLE QUERY

SELECT
  EXTRACT(DOW FROM created_at)::int AS weekday_number,
  COUNT(*) AS feedback_count
FROM product.feedback
GROUP BY weekday_number
ORDER BY weekday_number;

RESULT — exact output from the displayed Queryflo rows

weekday_number	feedback_count
1	2
2	1
4	1

PostgreSQL DOW values place two feedback rows on Monday, one Tuesday, and one Thursday.

Now You Try

Practice this concept

Product wants feedback volume by weekday number.

Available schema

product

Prefix tables with product.table_name.

weekday_numberfeedback_count

product.feedback

Column	Type
id	integer
user_id	integer
feature_id	integer
rating	integer
nps_score	integer
comment	text
created_at	timestamp with time zone
moderation_bucket	text

query.sql

Intermediate business practice