Group by created_at date_trunc to the day level in postgres

Using date_trunc to reduce time precision date_trunc is one of many date/time functions built into Postgres, but certainly scores high in its bang for the buck for me. date_trunc truncates timestamps to a certain level of precision, such as a specific hour, second, day, week, year, or so on. As an example, let's create a very simple orders table which stores solely the time an order is created:

CREATE TABLE orders (created_at timestamp); INSERT INTO orders VALUES   ('2019-01-01 04:05:33'),   ('2019-01-01 09:05:51'),   ('2019-01-03 02:55:14'),   ('2019-01-08 14:12:07');

Now, we can use date_trunc upon created_at to, say, group and count orders by day or week:

SELECT date_trunc('day', created_at) AS day,        COUNT(created_at) FROM orders        GROUP BY day        ORDER BY day;

day                 | count ---------------------------- 2019-01-01T00:0 ... | 2 2019-01-08T00:0 ... | 1 2019-01-03T00:0 ... | 1

This basic example shows that if we group by created_at truncated to the day level, we can count how many orders there were on each unique day.