Badge for posts created between 1 AM and 5 AM

dev
#1

I’m afraid I became addicted to these badges :smiling_face_with_three_hearts:
So this one it will be granted to users who created posts between 1 AM and 5 AM, updated daily.
But unfortunately, I have no idea how to select that interval. Any help is appreciated.

SELECT user_id, current_timestamp granted_at 
FROM badge_posts p  
WHERE p.created_at > CURRENT_DATE /* How to between '1AM' and '5AM' ? */ 
GROUP BY user_id 
HAVING count(*) >= 100

image

3 Likes
#2

It is possible to filter as follows:

SELECT 
    user_id, 
    current_timestamp granted_at 
FROM badge_posts p  
WHERE p.created_at::time BETWEEN '01:00' AND '05:00'
GROUP BY user_id 
HAVING count(*) >= 100

I hope it helps

2 Likes
#3

Thanks a lot for your help. I tried your example but no badge is granted in the preview. Is 11:17 AM here and I’ve changed the time in your example between 10:00 and 13:00 and created a demo topic and reply but nothing shows up. I tried also:

SELECT 
    user_id, 
    current_timestamp granted_at 
FROM badge_posts p  
WHERE p.created_at:: time >= '10:00:00' or p.created_at:: time < '13:00:00' 
GROUP BY user_id 
HAVING count(*) >= 100

And still nothing shows up :thinking:

1 Like
#4

The database records the timestamp at GMT+0, so you need to add that time difference to your time zone. If you tell me your time zone, I can adjust it for you.

2 Likes
#5

Great. It’s Europe/Brussels, GMT +01:00.

3 Likes
#6

You are not receiving the badge because you have added the HAVING count(*) >= 100 constraint, so you will only be able to earn the badge if you make more than 100 posts between that time interval.

SELECT 
    user_id, 
    current_timestamp granted_at 
FROM badge_posts p  
WHERE p.created_at::time BETWEEN '02:00' AND '06:00'
GROUP BY user_id 
HAVING count(*) >= 100
3 Likes
#7

btw Nice badge-design: Is it CSS-styled SVG or a pixelbased graphic?

3 Likes
#8

OMG you’re right I forgot about that. It works. If I’m not asking too much, is it possible to have that time check based on the user’s chosen time on his profile? Otherwise, the forum will be full of “Watchmens” badges :smile: ( for people living outside of Europe etc ). Thanks a lot!

#9

Made in Photoshop and Figma, not CSS styled. I’ll post the URL on this forum soon as I’ll finish the design and all the badges.

3 Likes
#10

Considering the user’s timezone, the query will look like this:

WITH users_timezone AS (
    SELECT 
        user_id, 
        CASE WHEN utc_offset IS NULL THEN '00:00:00' 
             ELSE utc_offset END
    FROM user_options uo
    LEFT JOIN pg_timezone_names pt ON uo.timezone = pt.name
)

SELECT 
    p.user_id,
    current_timestamp granted_at
FROM badge_posts p  
INNER JOIN users_timezone ut ON ut.user_id = p.user_id
WHERE (p.created_at+ut.utc_offset)::time BETWEEN '01:00' AND '05:00'
GROUP BY p.user_id 
HAVING count(*) >= 100
4 Likes
Please create a subcategory for badges
#11

Thank you so much! :blush:

1 Like