SQL Date Functions: Group by Month, Filter Periods and Compare Years

Group revenue by month

Each database has its own function to truncate a date. PostgreSQL uses DATE_TRUNC, MySQL uses DATE_FORMAT, and SQL Server uses DATEFROMPARTS or FORMAT. Truncating to the first day of the month keeps the result sortable as a real date.

-- PostgreSQL
SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month, SUM(amount)
FROM orders GROUP BY month ORDER BY month;

Filter a period without killing the index

Writing WHERE YEAR(created_at) = 2025 applies a function to every row, so the database cannot use the index on created_at and scans the whole table. Use a half-open range instead: it is index-friendly and handles timestamps with hours correctly.

SELECT *
FROM orders
WHERE created_at >= '2025-01-01'
  AND created_at <  '2026-01-01';

Last 30 days and rolling windows

Use the current date function of your engine: CURRENT_DATE - INTERVAL '30 days' in PostgreSQL, CURDATE() - INTERVAL 30 DAY in MySQL, DATEADD(day, -30, GETDATE()) in SQL Server. Decide whether "30 days" includes today; dashboards and finance teams often disagree, and the difference shows up in totals.

Year-over-year comparison

Aggregate by month first, then use LAG with an offset of 12 to fetch the same month of the previous year. This avoids a self-join and gives the growth rate in one pass. NULLIF prevents a division by zero for new products.

WITH m AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS revenue_last_year,
       ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
             / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM m
ORDER BY month;

Months with no sales

GROUP BY only returns months that have rows, so a month with zero sales disappears from the chart. Generate a calendar (generate_series in PostgreSQL, a recursive CTE elsewhere) and LEFT JOIN your aggregates onto it with COALESCE(revenue, 0). Time zones matter too: store timestamps in UTC and convert with AT TIME ZONE before truncating if the report is local.