SQL
Filter and Group Data by Date Periods
Query and group data by specific date ranges (e.g., daily, monthly, yearly) using SQL functions, essential for time-series analysis in web dashboards.
SELECT
DATE_TRUNC('month', order_date) AS month_start,
COUNT(order_id) AS total_orders,
SUM(amount) AS total_revenue
FROM
orders
WHERE
order_date >= '2023-01-01' AND order_date < '2024-01-01'
GROUP BY
month_start
ORDER BY
month_start;
How it works: This snippet illustrates how to filter data by a date range and then group it by a specific time period (e.g., month, year). `DATE_TRUNC('month', order_date)` (PostgreSQL/Oracle syntax) truncates the date to the beginning of the month, allowing for grouping. Similar functions exist in other databases (e.g., `DATE_FORMAT(order_date, '%Y-%m-01')` in MySQL or `DATEADD(month, DATEDIFF(month, 0, order_date), 0)` in SQL Server). This is crucial for generating time-series reports and analytics in web applications.