Dates and Cohorts

Dates, Cohorts, and Time Windows

The one idea: Most "these two numbers don't match" problems come from dates, not from broken math. Say exactly which days a query includes, use a start-to-next-start range instead of BETWEEN, and know whether you're grouping people by the calendar or by when they first showed up.

⚠️ The trap: BETWEEN '2026-06-01' AND '2026-06-30' covers all of June. That feels right and it's wrong. **On a timestamp column that end date means midnight at the start of June 30, so BETWEEN silently drops almost the entire last day; use a half-open range instead: >= '2026-06-01' AND < '2026-07-01'.**

When two dashboards disagree, the cause is almost never a broken JOIN or a bad SUM. It's dates. Two queries that both claim to show "June revenue" can differ by several percent purely from how each one drew the box around June — and both authors will swear theirs is right. Date logic is the single most common source of "this number doesn't match," and it's also the most learnable: there are only a handful of ways to get it wrong, and they repeat forever.

DATE_TRUNC: the workhorse

DATE_TRUNC rounds a timestamp down to the start of a period:

SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(order_total) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1

2026-06-17 14:32:09 becomes 2026-06-01 00:00:00. Every order in June collapses to the same value, so grouping by it gives one row per month. You'll see 'week', 'month', 'quarter', 'year' — in PostgreSQL, 'week' truncates to Monday. When you read a weekly dashboard, it's worth knowing whether weeks start Monday (Postgres default) or Sunday (many BI tools),…