Forgly

SQL to group by hour

Summarize rows per hour by truncating the timestamp to the hour.

PostgreSQL
SELECT DATE_TRUNC('hour', created_at) AS hour,
       COUNT(*) AS events
FROM events
GROUP BY DATE_TRUNC('hour', created_at)
ORDER BY hour;
MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS hour,
       COUNT(*) AS events
FROM events
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00')
ORDER BY hour;
SQL Server
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, created_at), 0) AS hour,
       COUNT(*) AS events
FROM events
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, created_at), 0)
ORDER BY hour;

DATEDIFF(HOUR, 0, …) counts whole hours since the epoch; adding them back to 0 rebuilds a clean top-of-the-hour timestamp.

SQLite
SELECT strftime('%Y-%m-%d %H:00:00', created_at) AS hour,
       COUNT(*) AS events
FROM events
GROUP BY strftime('%Y-%m-%d %H:00:00', created_at)
ORDER BY hour;

How to group records by hour

Grouping by hour buckets every row into the clock hour it happened in. Truncate the timestamp to the hour, then aggregate — the function differs by dialect.

How it works

  • Truncating the timestamp to the hour gives one identical value for every row in that clock hour.
  • Keep the date in the bucket (not just the hour number) so the same hour on different days stays separate.
  • Group by 24-hour-of-day instead (EXTRACT(HOUR FROM …)) when you want a daily traffic-by-hour profile rather than a per-day timeline.

Tip

Convert to a consistent time zone before truncating — otherwise an hour bucket can straddle a DST change or split traffic across zones.

Related SQL queries

Frequently asked questions

How do I group records by hour in SQL?

Grouping by hour buckets every row into the clock hour it happened in. Truncate the timestamp to the hour, then aggregate — the function differs by dialect. Truncating the timestamp to the hour gives one identical value for every row in that clock hour. Keep the date in the bucket (not just the hour number) so the same hour on different days stays separate. Group by 24-hour-of-day instead (EXTRACT(HOUR FROM …)) when you want a daily traffic-by-hour profile rather than a per-day timeline.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.