1

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.

2
  • The question is misleading without stating your (outdated) version or Postgres. Always declare your version number. Commented Dec 27, 2016 at 2:31
  • @ErwinBrandstetter: Sound advice. Commented Dec 27, 2016 at 16:52

2 Answers 2

3

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.

0
3

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
4
  • 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? Commented Dec 26, 2016 at 11:57
  • @HassanBaig: sorry, copy & paste error. For future questions, always include the Postgres version, especially when using old versions
    – user1822
    Commented Dec 26, 2016 at 11:58
  • 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. Commented Dec 26, 2016 at 12:00
  • 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. Commented Dec 26, 2016 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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