Aggregate Data by Custom Time Intervals (e.g., Hourly, Daily)

SELECT
    DATE_TRUNC('day', created_at) AS day_interval,
    COUNT(*) AS total_orders,
    SUM(order_total) AS total_revenue
FROM
    orders
GROUP BY
    DATE_TRUNC('day', created_at)
ORDER BY
    day_interval;

How it works: This snippet illustrates how to aggregate data by specific time intervals, a common requirement for generating reports and analyzing trends. Using functions like DATE_TRUNC (PostgreSQL), TRUNC (Oracle), or CAST(AS DATE) / DATE_FORMAT (MySQL/SQL Server), you can effectively round down a timestamp to the beginning of an hour, day, month, or year. Grouping by this truncated value allows you to count, sum, or average metrics for each interval, providing insights into activity patterns over time.