Devotee Badge and Timezones, relaxing requirements?

feature
1

Had a couple of user reports regarding the Devotee badge, that requires 365 days of consecutive logins to gain. The issue seems to be that people on timezones far away from UTC (say, PST +8) have to take that into account that the underlying ‘day’ is in UTC times. We are seeing European users getting the badge, while others not understanding why they are not.

What I think has happened is that someone on say PST +8 will login early morning on March 14th, then late night on March 15th (to them a login each day) but the system badge query will see March 15th as entirely ‘skipped’, breaking their 365 consecutive days run. This can be upsetting for the end user, especially if they are close to the end of their year ‘run’.

I know that timezones and database queries often don’t mix well, but I’d ask if anything could be done to ‘widen’ the query, perhaps to give tolerance for a ‘UTC day’ when it’s longer badge periods like a year - thanks.

8 Likes
2

The table that stores visits use a plain date, not a timestamp, so implementing a tolerance of a few hours is not feasible.

It all depends on what your community wants in the end. You could disable that badge and create an equivalent custom one that triggers with less days so it is enough to cover those days. Or check for users whose visit gaps are never more than 1 day.

7 Likes
3

Thanks for looking and the response.

They want their badge to work regardless of their timezone. The long time users are the most passionate ones. :slight_smile:

We have a Data Explorer query like this, which helped figure out the issue:

-- [params]
-- user_list :users

WITH StartingPoints AS (
  SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
  FROM user_visits AS A
  WHERE NOT EXISTS (
     SELECT 1
     FROM user_visits AS B
     WHERE B.visited_at = A.visited_at - INTERVAL '1 day' AND
     B.user_id = A.user_id
  ) AND user_id IN (:users)
),
EndingPoints AS (
  SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
  FROM user_visits AS A
  WHERE NOT EXISTS (
     SELECT 1
     FROM user_visits AS B
     WHERE B.visited_at = A.visited_at + INTERVAL '1 day' AND
     B.user_id = A.user_id
  ) AND user_id IN (:users)
)
SELECT u.username, S.visited_at AS start_range, E.visited_at AS end_range, (E.visited_at - S.visited_at + 1) AS Days
FROM StartingPoints AS S
JOIN EndingPoints AS E ON E.rownum = S.rownum
JOIN users u ON u.id=S.user_id AND
u.id IN (:users)
ORDER BY u.id ASC, S.visited_at DESC

…which gives the continuous segments listed. Because of the UTC part (or rather how the underlying date is stored rather than datetime?), it seems fair that we should award the badge, even if a ‘1 day gap’ exists I think.

We’ll look at trying to create another Devotee badge based off of changing this query, perhaps with a ‘2 day’ INTERVAL?

5 Likes
4

We receive a few support requests about the Devotee badge for exactly this reason. The work around is to grant the badge through the Rails console. Instructions for doing that are given here: Award a non-custom badge through the console.

If there is an easy way to relax the badge’s criteria to allow for a few skipped days, I think that would be a good idea.

5 Likes