current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 2 down vote favorite

I need to find out all the users who registered for my Postgresql 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.

share|improve this question
    
The question is misleading without stating your (outdated) version or Postgres. Always declare your version number. – Erwin Brandstetter 2 hours ago
up vote 2 down vote accepted

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.

share|improve this answer
up vote 3 down vote

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
share|improve this answer
    
I'm on 9.3, so that's applicable for me. Thanks for the answer! Btw, wouldn't that be count (case when... and not count(when when.... in the second line for pre-9.4? – Hassan Baig 17 hours ago
    
@HassanBaig: sorry, copy & paste error. For future questions, always include the Postgres version, especially when using old versions – a_horse_with_no_name 17 hours ago
    
Sound advice. So this would give me the two totals in a single command. I actually wanted to get the difference of the two totals in the same command, instead of having to calculate that manually. – Hassan Baig 17 hours ago
    
As in, 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. – Hassan Baig 16 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.