SQL
Group Data by Specific Date Intervals (Day, Month, Year)
Learn to aggregate and group your SQL data by various time intervals like day, week, month, or year for effective time-series analysis and reporting.
-- PostgreSQL / MySQL (for grouping by month)
SELECT
DATE_TRUNC('month', order_date) AS month, -- PostgreSQL
-- DATE_FORMAT(order_date, '%Y-%m-01') AS month, -- MySQL
COUNT(order_id) AS total_orders,
SUM(total_amount) AS monthly_revenue
FROM
orders
GROUP BY
month
ORDER BY
month;
-- SQL Server (for grouping by month)
-- SELECT
-- DATEADD(month, DATEDIFF(month, 0, order_date), 0) AS month,
-- COUNT(order_id) AS total_orders,
-- SUM(total_amount) AS monthly_revenue
-- FROM
-- orders
-- GROUP BY
-- DATEADD(month, DATEDIFF(month, 0, order_date), 0)
-- ORDER BY
-- month;
How it works: This snippet illustrates how to group and aggregate data based on specific date intervals. It shows examples for grouping by month using DATE_TRUNC (PostgreSQL), DATE_FORMAT (MySQL), and DATEADD/DATEDIFF (SQL Server). This technique is essential for generating time-series reports, such as monthly sales summaries or daily user registrations.