postgres weekday frequency by hours

Question

I would like to generate a report like the one below (is a screenshot from the Google Analytics App for Android)

I have events that occurs 10-15 times a day, and I'd like to see, for each weekday, the frequency grouped by hours, from the beginning.

I only need to use a DateTime (timestamp) field named "created_at" (is a Rails project)

How can I do it in PostgreSQL?

Thank you

Answer 1

The already provided answers are both correct, but just for variety here is one that uses the 'tablefunc' extension to cross-tabulate the result so it looks exactly as per your sample.

Before using it you must create the tablefunc extension (which is available in the contrib package of postgresql):

CREATE EXTENSION IF NOT EXISTS tablefunc;

Here is the query, assuming that the input data is in column created_at of table t:

SELECT * FROM CROSSTAB($$SELECT h.hour AS hour_of_day,
    dow.day AS day, 
    COUNT(t.created_at)::INT
FROM (values('Mon'),('Tue'),('Wed'),('Thu'),('Fri'),('Sat'),('Sun')) AS dow(day)
CROSS JOIN generate_series(0,23) as h(hour)
LEFT JOIN t ON to_char(t.created_at, 'Dy')=dow.day AND extract(hour from t.created_at)=h.hour
GROUP BY dow.day,h.hour
ORDER BY h.hour,dow.day$$) AS d(Hour int, "Mon" int,"Tue" int,"Wed" int,"Thu" int,"Fri" int,"Sat" int,"Sun" int);

Key points to note:

• The query cross joins the set of days with the set of hours 00-23 so that all cells in the output are present even if there is no data in the input table for a given cell
• The crosstab function takes an SQL query as input and cross tabulates the result, producing a record set
• For reasons not completely clear to me I had to cast the return value from count (I guess it comes back as BIGINT?)
• You (unfortunately) have to spell out the column names in the result via the AS clause as shown

Here is the result:

 hour | Mon | Tue | Wed | Thu | Fri | Sat | Sun
------+-----+-----+-----+-----+-----+-----+-----
    0 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    1 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    2 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    3 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    4 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    5 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    6 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    7 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    8 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    9 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   10 |   0 |   0 |   0 |   0 |   0 |   0 |   1
   11 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   12 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   13 |   0 |   0 |   0 |   1 |   0 |   0 |   0
   14 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   15 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   16 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   17 |   0 |   0 |   1 |   0 |   0 |   0 |   0
   18 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   19 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   20 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   21 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   22 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   23 |   0 |   0 |   0 |   0 |   1 |   0 |   0
(24 rows)

Which is generated from this sample data:

         created_at
----------------------------
 2014-06-12 23:06:03.746884
 2014-01-15 10:00:00
 2014-05-25 13:00:00
 2014-03-01 17:00:00
(4 rows)

Answer 2

This is quite easy. You just have to group by the hour of the day and the date of the day and then count, how many elements are there. The first two columns of the result (hour of the day and date) are the 2D coordinates of the corresponding cell in your diagram. The third column (the count) gives you the color of that cell.

An example:

SELECT 
   extract('hour' FROM starttime) as hour, 
   date_trunc('day', starttime) as day,
   count(*) as nbmr
FROM actions
GROUP BY hour, day;

(Live fiddle)

In this example, the columns "hour" and "day" correspond to the y- and x-axis of a cell in your diagram. The column "nmbr" then tells you the color of that cell.

You can easily modify this query, e.g. to show percentages and group by weekday (0 is Sunday):

SELECT 
   extract('hour' FROM starttime) as hour, 
   extract('dow' FROM starttime) as day,
   count(*) * 100.0 / (select count(*) from actions) as nbmr
FROM actions
GROUP BY hour, day;

(Live fiddle)