Our self-hosted site requires all users to be registered, no anon, and all accounts mod approved, typically within an hour or two of request.
We noticed two new users, one with 8 hours since creation, being reported as 2 Days Visited. Another with 1 day ago creation reporting 3 Days Visited.
Searched and found the “Days Visited Anomaly” post but that does not appear relevant. What should I check to see what is wrong, or what we are doing wrong?
Sorry for the delayed response. A little bit of excess rainfall in Santa Barbara had me diverted.
All our users self-register, so accounts are created that way, and then require approval by a mod before they can access the forum. I had thought there might be a “wrap midnight” issue in that a user reg’s at 2100 then ges approved at 0100 showing up as two days, but there is the 3 day user I posted.
I don’t know how long this has been happening, as I don’t look that closely, but one of our mods did. I am keeping an eye on our incoming registrations to see if this happens with each new user.
If there a a Data Explorer query that will help here, please let me know, and I will run it and report back.
But the 3 day user was created more than a day ago. So they could also register before midnight (day 1), visit some hours later (day 2) and a day later (day 3). That would be less than two days, so showing one day is correct.
I couldn’t find an existing one, but maybe something like this:
SELECT u.id AS user_id,
u.created_at,
u.approved_at,
us.days_visited
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE u.approved_at IS NOT NULL
ORDER BY u.created_at DESC
Or one with a little extra to make it more readable:
SELECT u.id AS user_id,
CONCAT(u.created_at::date, ' at ', to_char(u.created_at, 'HH:MM')) "user_created",
CONCAT(u.approved_at::date, ' at ', to_char(u.approved_at, 'HH:MM')) "user_approved",
us.days_visited
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE u.approved_at IS NOT NULL
ORDER BY u.created_at DESC
Thank U! Just had a new user register so I got a chance to approve user and run it. Mods access to the query so hopefully we will see some info that is helpful in the future.
I think the “Days Visited” value changes once it hits exactly 12:00 AM UTC. So a user can join before it hits 12:00 AM UTC then be active until the time has been hit (then it would increase the days visited)
Good to see it happens here at Meta. I hate it when I’m the only one, then u know u r doing it wrong
I interpreted the “Created: 1 day ago” as yesterday, since it appears the creation timestamp on the account is when the user registered, not when they are approved (which makes sense since the registration is a visit). I can see a “2 days visited” with a UTC rollover but could not get to “3 days visited”. If that were the case, wouldn’t “Created” would be “2 days ago”?.
That “3 day user” has not been back since Jan 11, so the current reporting looks correct - Jan 9 to Jan 11.
But the Data Explorer shows a creation of Jan 10, not Jan 9 as in the User Activity. Is there some local time vs UTC thing happening? Perhaps noting in the time reporting what the TZ is? These two queries were run a minute apart.
Does Discourse log what if considers a “visit” and the duration? If so I could query a user’s date/time on the site? This would be lots of data on a busy site, so maybe only retain the last X days? That would allow mapping on a timeline.
I could create a few test accounts and see what happens on reporting, It’s not a big deal though it makes one wonder about other stats if we don’t understand the accounting behind the reporting.
Yes. When viewing a profile it shows the times in your local TZ instead of UTC unless your local TZ happens to be UTC whereas DE shows the stored UTC value
Edit: I did some testing on the “3 day” user I posted earlier, modifying the query to include first and last seen. He was logged on for just under 44 hours, spanning three 00:00 UTC midnights, hence the three days.
It appears something strange is happening when displaying the User Activity, as reporting “created 1 day ago” but having “Days Visited 3” is incongruous. This sounds like a result of over-simplifying the data.
IMO it would be better to simply report the UTC timestamps as-is and let the user interpret. “1 day ago” is indeterminate. How many hours in the past does it take to hit one day? It sounds like crossing 0000 UTC is enough. So a user created at 2359 UTC is “1 day ago” when looking at User Activity at 0001 UTC?
That may be technically correct - if I’m in the UTC TZ that user was created “yesterday, i.e. 1 day ago” but anywhere else, that is not correct, or not how it would be understood. I’m not sure that is really useful in understanding the activity from an admin viewpoint.
And I realize my understanding is incomplete still, so thank you all for continuing my education. For now, I have advised our mods to use the DE query to understand a users events if they need detailed accuracy.
I’ll slip this over to ux as nothing seems to be broken (though maybe the information could be displayed with more clarity). But if you find anything else later we can always slide it back again.
