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]

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

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 = 100 And still nothing shows up :thinking:

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.

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

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

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!

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.

[image] cosdesign: is it possible to have that time check based on the user’s chosen time on his profile? 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

Badge for posts created between 1 AM and 5 AM

Development

Matze February 8, 2021, 10:48am 7

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

Topic		Replies	Views
SQL Badge Query - Quick Responder! Data & reporting sql-triggered-badge	2	1228	December 7, 2015
SQL query for users who have posted on a date (or date range) Data & reporting sql-query	7	1654	October 18, 2018
Badge granted to our staff members that have read the guidelines placed in our hidden staff category Data & reporting sql-triggered-badge	11	368	March 3, 2015
A badge for reading time? Data & reporting sql-triggered-badge	2	703	February 5, 2021
Badge for reading a topic? Data & reporting sql-triggered-badge	6	568	October 9, 2023

Badge for posts created between 1 AM and 5 AM

Related topics