Is this the latest-row-per-group bug?

No. That one picks the wrong sibling columns after MAX(date). This one never collapses timestamps into a day, so 'daily' output is one group per distinct instant.

The total of all those rows is right. Is that fine?

The grand total can be right while every 'day' is wrong. A chart, a top day, or an average per day will be nonsense. Sum the groups and compare to SUM(amount) with no GROUP BY before you trust a single day.

What about time zones?

DATE_TRUNC('day', created_at) uses the session timezone for timestamptz. Truncating in UTC and labeling the bucket 'Bangkok day' shifts the boundary. Name the zone, then truncate.

GROUP BY created_at::date versus DATE_TRUNC?

Both collapse to a calendar day. ::date returns a date. DATE_TRUNC returns a timestamp at midnight. Either is a day. GROUP BY created_at is not.

Why did the model do this?

You said 'by day' and the column it saw was created_at. It grouped by the column it had, not by the grain you named.

GROUP BY Timestamp Not Day | DATE_TRUNC

You ask for "revenue by day this month." You get 400 rows. Each "day" is a few dollars. The month has 30 days. GROUP BY created_at kept the time. Every distinct second is its own bucket.

A timestamp is not a day

-- one group per exact instant
SELECT created_at, SUM(amount)
FROM orders
GROUP BY created_at

-- one group per calendar day
SELECT DATE_TRUNC('day', created_at) AS day, SUM(amount)
FROM orders
GROUP BY 1

Two orders at 10:00:01 and 10:00:02 are two groups. The SQL runs. A spreadsheet or a chart treats each row as a day, so "our best day" is whichever second happened to hold the biggest single charge.

This is not HAVING vs WHERE (the threshold never runs) and not latest row per group (the other columns came from the wrong sibling). The grain of the GROUP BY is just finer than the English.

GROUP BY DATE(created_at) in MySQL and CAST(created_at AS date) in Postgres are the same fix. GROUP BY created_at::date still needs the timezone you mean if the column is timestamptz — otherwise the "day" boundary is UTC midnight. Pair that with the timezone note.

How to catch it

COUNT(*) of the grouped result
vs COUNT(DISTINCT created_at::date) over the same filter
If the first is much larger, you grouped by the timestamp.
Also: SUM of the group totals must equal SUM(amount) with no GROUP BY.

If the row count is close to the number of orders, nothing was aggregated by day at all.

The prompt that heads this off

When I say by day / by week / by month, group by a truncated timestamp
(DATE_TRUNC or a date cast), not by the raw created_at column.
Show the number of groups. A month of days should be about 28–31 rows,
not hundreds.

The short version

GROUP BY created_at means "by this exact time." "By day" means one bucket per date. If daily revenue has a row for every order, the time never got truncated.

Read-only MCP gateway: mcpserver.design.

Related reading: