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.

Intermediate business practice