CODEWITHSUNDEEP
databasepostgresqldatetime
Anurag
Anurag

Reputation: 13

How to group by data in postgresql by timestamp and distinct values?

This is my current table format :

userName userId recordedAt
Deepesh    1    2021-03-29 07:20:36
Sushant    2    2021-03-29 07:27:55
Ankita     3    2021-03-29 07:30:40
Aman       4    2021-03-29 07:39:15
Ankita     3    2021-03-29 07:51:29
Suman      5    2021-03-29 07:55:19
Ankita     3    2021-03-29 08:36:55

I want to query data in such a way that it should count the distinct userId and then group it by hour. Expected result -

time   userLogged
07:00      5
08:00      1

Upvotes: 1

Views: 920

Answers (2)

Prashant
Prashant

Reputation: 28

SELECT recordedAt, COUNT(DISTINCT userId) AS userLogged FROM table_name GROUP BY recordedAt;

Upvotes: 0

Andi
Andi

Reputation: 21

When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc. The PostgreSQL function you need here is date_trunc.

select
  date_trunc('minute', created_at), -- or hour, day, week, month, year
  count(1)
from users
group by 1

Upvotes: 0

Related Questions