Forgly

SQL to group by day

Summarize rows per calendar day by stripping the time off a timestamp.

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

Grouping by day means dropping the time portion so every row from the same date lands in one bucket. Each dialect has a direct date cast or function.

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?

Grouping by day means dropping the time portion so every row from the same date lands in one bucket. Each dialect has a direct date cast or function. 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.