Forgly

SQL to group by day

Group rows by day in SQL — strip the time off a timestamp to count or sum records per calendar day.

PostgreSQL
SELECT created_at::date AS day,
       COUNT(*) AS orders
FROM orders
GROUP BY created_at::date
ORDER BY day;
MySQL
SELECT DATE(created_at) AS day,
       COUNT(*) AS orders
FROM orders
GROUP BY DATE(created_at)
ORDER BY day;
SQL Server
SELECT CAST(created_at AS DATE) AS day,
       COUNT(*) AS orders
FROM orders
GROUP BY CAST(created_at AS DATE)
ORDER BY day;
SQLite
SELECT DATE(created_at) AS day,
       COUNT(*) AS orders
FROM orders
GROUP BY DATE(created_at)
ORDER BY day;

How to group records by day

To group by day in SQL, strip the time off each timestamp so every row from the same date lands in one bucket, then GROUP BY it. Copy-paste examples below for PostgreSQL (::date), MySQL and SQLite (DATE()), and SQL Server (CAST AS DATE) — each counts rows per calendar day.

How it works

  • Casting a timestamp to a date keeps the calendar day and discards hours, minutes and seconds.
  • Grouping by that date buckets every event on the same day together, regardless of time.
  • Mind the time zone: convert to one consistent zone first, or late-night rows can fall on a different day than users expect.

Tip

Cast to DATE rather than formatting to a string where you can — dates sort and range-filter faster and let the query keep using an index on the timestamp column.

Related SQL queries

Frequently asked questions

How do I group records by day in SQL?

To group by day in SQL, strip the time off each timestamp so every row from the same date lands in one bucket, then GROUP BY it. Copy-paste examples below for PostgreSQL (::date), MySQL and SQLite (DATE()), and SQL Server (CAST AS DATE) — each counts rows per calendar day. Casting a timestamp to a date keeps the calendar day and discards hours, minutes and seconds. Grouping by that date buckets every event on the same day together, regardless of time. Mind the time zone: convert to one consistent zone first, or late-night rows can fall on a different day than users expect.

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.