Finding count of objects within a specified time window in SQL (Postgresql DB) 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 &gt; now() - interval'24 hours';
select count (*) from auth_user where date_joined &gt; 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 &gt; (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.
Use conditional aggregation:

select count(*) filter (where date_joined &gt; now() - interval'24 hours') as last_24_hours,
       count(*) filter (where date_joined &gt; 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 &gt; now() - interval'24 hours' then 1 end) as last_24_hours,
       count(case when date_joined &gt; now() - interval'48 hours' then 1 end) as last_48_hours
from auth_user