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

Answer 2

A simple group by would do the trick:

select 
  extract(dow from inserted_at)
  , count(*)
from conversations
where inserted_at between date '2016-08-08' and '2016-08-08' + interval '7 days'
group by 1;

And an improved version of that query (to make sure that days with count 0 are included as well):

with week as
(
   SELECT s.d day FROM generate_series(1,7) s(d)
)
select
  week.day
  , count(extract(dow from c.inserted_at))
from days 
left join conversations c on week.day = extract(dow from c.inserted_at)
     and c.inserted_at between now() and now() + interval '7 days'
group by week.day
order by week.day;

An index on inserted_at column would help to quickly select the relevant interval.