Group by day of week

Question

I have a table conversations with an inserted_at column

I want to draw a chart showing the amount of conversations created over time.

I'd like to be able to group the data by either the date, the day of week, and the time of date, to show possible trends.

I'll be using intervals of 7 days, 1 month and 6 months.

Example:

Interval: 1 month group by day of week

I'd like something like

| Monday | Tuesday | Wednesday | Thursday | Friday |
|--------|---------|-----------|----------|--------|
| 11     | 22      | 19        | 17       | 10     |

or interval: 7 days group by date

| 1/1 | 2/1 | 3/1 | 4/1 | 5/1 | 6/1 | 7/1 |
|-----|-----|-----|-----|-----|-----|-----|
| 11  | 22  | 19  | 17  | 10  | 10  | 7   |

What is the best way to accomplish this (examples would be greatly appreciated), and is PostgreSQL fit for these kind of queries?

Lastly, are there any special sort of indexes that will improve such queries?

Answer 1

Days of the week:

select
    count(extract(dow from inserted_at) = 1 or null) as monday,
    count(extract(dow from inserted_at) = 2 or null) as tuesday,
    count(extract(dow from inserted_at) = 3 or null) as wednesday,
    count(extract(dow from inserted_at) = 4 or null) as thursday,
    count(extract(dow from inserted_at) = 5 or null) as friday,
from conversations

count only counts not null values. false or null is null so only true will be counted.

In newer versions there is an aggregation filter:

count(*) filter (where extract(dow from inserted_at) = 4) as thursday