Postgres Group By Day

Let's say you have a table which shows the timestamp that somebody requested a particular url and you want to see a breakdown of usage per day.

Schema (PostgreSQL v13)

CREATE TABLE logs(
	id SERIAL PRIMARY KEY,
	url TEXT NOT NULL,
	request_timestamp TIMESTAMPTZ NOT NULL
);

Example Data

SELECT * FROM logs ORDER BY request_timestamp ASC;

| id  | url    | request_timestamp        |
| --- | ------ | ------------------------ |
| 1   | /      | 2021-03-28T12:01:02.000Z |
| 2   | /posts | 2021-03-28T12:01:34.000Z |
| 3   | /      | 2021-03-28T12:01:56.000Z |
| 4   | /links | 2021-03-28T12:02:10.000Z |
| 5   | /posts | 2021-03-29T13:57:46.000Z |

Count of logs per day

SELECT date_trunc('day', request_timestamp) AS day, COUNT (id) FROM logs GROUP BY day ORDER BY day ASC;

| day                      | count |
| ------------------------ | ----- |
| 2021-03-28T00:00:00.000Z | 4     |
| 2021-03-29T00:00:00.000Z | 1     |

Count of unique url requests per day

SELECT date_trunc('day', request_timestamp) AS day, COUNT (DISTINCT url) FROM logs GROUP BY day ORDER BY day ASC;

| day                      | count |
| ------------------------ | ----- |
| 2021-03-28T00:00:00.000Z | 3     |
| 2021-03-29T00:00:00.000Z | 1     |

Count of logs per url per day

SELECT date_trunc('day', request_timestamp) AS day, url, COUNT (id) FROM logs GROUP BY day, url ORDER BY day, url ASC;

| day                      | url    | count |
| ------------------------ | ------ | ----- |
| 2021-03-28T00:00:00.000Z | /      | 2     |
| 2021-03-28T00:00:00.000Z | /links | 1     |
| 2021-03-28T00:00:00.000Z | /posts | 1     |
| 2021-03-29T00:00:00.000Z | /posts | 1     |