SQL DATE_TRUNC by Month

Rounds down to the 1st of the current month — probably the single most common DATE_TRUNC precision in real reporting.

What & Why

DATE_TRUNC('month', date_column) lands on the 1st day of whatever month the date falls in. "Monthly active users," "revenue by month," and similar recurring business reports almost always group by exactly this expression.

See How It Works

BUSINESS QUESTION

Product wants monthly feedback totals.

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
  DATE_TRUNC('month', created_at)::date AS feedback_month,
  COUNT(*) AS feedback_count
FROM product.feedback
GROUP BY feedback_month
ORDER BY feedback_month;

RESULT — exact output from the displayed Queryflo rows

feedback_month	feedback_count
2024-01-01	1
2024-02-01	1
2024-03-01	1
2024-04-01	1

Each feedback row truncates to its actual month start.

Now You Try

Practice this concept

Product wants monthly feedback totals.

Available schema

product

Prefix tables with product.table_name.

feedback_monthfeedback_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