Badge for posts created between 1 AM and 5 AM

Development

AI-generated summary

A discussion is taking place about creating a badge for posts created between 1 AM and 5 AM. The initial query was to select posts within this time interval, but the author, cosdesign, was unsure how to implement it. michebs provided a solution using BETWEEN '01:00' AND '05:00'. However, the badge wasn’t granted due to a time zone issue, as the database records timestamps at GMT+0. After adjusting for the user’s time zone (Europe/Brussels, GMT+01:00), the badge still wasn’t granted because of a HAVING count(*) >= 100 constraint. michebs pointed out the issue and provided a revised query.

Further discussion led to the question of making the time check based on the user’s chosen time zone on their profile. michebs provided a revised query that takes into account the user’s time zone. The query joins the badge_posts table with a users_timezone table, which includes the user’s UTC offset. The badge is now awarded based on the user’s local time, rather than the server’s time zone.

The discussion also touched on the design of the badge, with Matze complimenting the design and cosdesign sharing that it was created in Photoshop and Figma.

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
image412×190 8.51 KB

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

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:

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.

5

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

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
7

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

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.

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
11

Thank you so much! :blush: