Count distinct dates between two timestamps

Question

I want to count %days when a user was active. A query like this

select 
    a.id, 
    a.created_at, 
    CURRENT_DATE - a.created_at::date as days_since_registration, 
    NOW() as current_d
from public.accounts a where a.id = 3257

returns

id  created_at  days_since_registration current_d   tot_active
3257    2022-04-01 22:59:00.000 1   2022-04-02 12:00:0.000 +0400    2

The person registered less than 24 hours ago (less than a day ago), but there are two distinct dates between the registration and now. Hence, if a user was active one hour before midnight and one hour after midnight, he is two days active in less than a day (active 200% of days)

What is the right way to count distinct dates and get 2 for a user, who registered at 23:00:00 two hours ago?

You could subtract the timestamps and use [date_part](https://www.sqlines.com/postgresql/how-to/datediff) for this. See also the answers given [here](https://stackoverflow.com/questions/24929735/how-to-calculate-date-difference-in-postgresql). — T_01, Apr 02 '22 at 08:47

score 1 · Accepted Answer · answered Apr 02 '22 at 08:50

WITH cte as (
    SELECT 42 as userID,'2022-04-01 23:00:00' as d
    union 
    SELECT 42,'2022-04-02 01:00:00' as d
)
SELECT 
   userID,
   count(d),
   max(d)::date-min(d)::date+1 as NrOfDays,
   count(d)/(max(d)::date-min(d)::date+1) *100 as PercentageOnline
FROM cte
GROUP BY userID;

output:

userid	count	nrofdays	percentageonline
42	2	2	100

Count distinct dates between two timestamps

1 Answers1