postgresql database

Question

i wanna to make a query that select users that have same username and same hour of creation date by using postgresql database

Answer 1

Something like this should do the trick. This will return any user/hour pair along with the count (untested):

select users.username, datepart('hour', users.created_at), count(*) from users
  inner join users u2 
    on users.username = u2.username
    and datepart('hour', users.created_at) = datepart('hour', u2.created_at)
group by users.username, datepart('hour', users.created_at) having count(*) > 1

Answer 2

select u.*
from users u
join (
    select username, date_trunc('hour', creation_timestamp)
    from users
    group by 1, 2
    having count(*) > 1
) as x on u.username = x.username
order by u.username;

Should work nicely.