Finding count of objects within a specified time window in SQL (Postgresql DB)

Question

I need to find out all the users who registered for my Postgresql 9.3 backed website in the 24 hour window 2 days ago. Currently, I'm doing that via running the following queries, and then manually subtracting the difference:

select count (*) from auth_user where date_joined > now() - interval'24 hours';
select count (*) from auth_user where date_joined > now() - interval'48 hours';

How do I do everything in the same SQL query, including the subtraction? Thanks in advance!

---

If I do select count (*) from auth_user where date_joined > (now() - interval'48 hours') - (now() - interval'24 hours');, I get:

No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

How about this:

SELECT COUNT(*)
FROM auth_user
WHERE (date_joined >= NOW() - INTERVAL '48 hours') AND
    (date_joined <= NOW() - INTERVAL '24 hours')

There's also a BETWEEN syntax that might feel more natural:

SELECT COUNT(*)
FROM auth_user
WHERE date_joined BETWEEN
    NOW() - INTERVAL '48 hours' AND
    NOW() - INTERVAL '24 hours'

Here is the PostgreSQL docs page where BETWEEN is described.

Answer 2

Use conditional aggregation:

select count(*) filter (where date_joined > now() - interval'24 hours') as last_24_hours,
       count(*) filter (where date_joined > now() - interval'48 hours') as last_48_hours
from auth_user

The filter clause is available since Postgres 9.4, for older versions you need to use a case statement:

select count(case when date_joined > now() - interval'24 hours' then 1 end) as last_24_hours,
       count(case when date_joined > now() - interval'48 hours' then 1 end) as last_48_hours
from auth_user