새벽 1시부터 5시 사이에 작성한 게시글 배지

개발
1

이 배지들에 중독된 것 같습니다 :smiling_face_with_three_hearts:
그래서 이 배지는 오전 1시부터 오전 5시 사이에 게시글을 작성한 사용자에게 매일 갱신되어 지급될 예정입니다.
하지만 불행히도 해당 시간대를 선택하는 방법을 전혀 모르겠습니다. 도움을 주시면 감사하겠습니다.

SELECT user_id, current_timestamp granted_at 
FROM badge_posts p  
WHERE p.created_at > CURRENT_DATE /* '오전 1시'와 '오전 5시' 사이를 어떻게 표현할까요? */ 
GROUP BY user_id 
HAVING count(*) >= 100

image
image412×190 8.51 KB

2

다음과 같이 필터링할 수 있습니다:

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

도움이 되길 바랍니다

3

도와주셔서 정말 감사합니다. 예시를 시도해 보았지만 미리보기에 배지가 표시되지 않습니다. 여기 시간은 오전 11:17이고, 예시에서 시간을 10:00~13:00 사이로 변경한 후 데모 주제와 답글을 생성해 보았지만 아무것도 표시되지 않습니다. 다음도 시도해 보았습니다:

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

그래도 아무것도 표시되지 않습니다 :thinking:

4

데이터베이스는 GMT+0 기준으로 타임스탬프를 기록하므로, 해당 시간 차이를 사용자의 시간대에 추가해야 합니다. 시간대를 알려주시면 그에 맞게 조정해 드리겠습니다.

5

좋아요. Europe/Brussels, GMT +01:00입니다.

6

HAVING count(*) >= 100 제약 조건을 추가했기 때문에 배지를 받지 못하고 있습니다. 따라서 해당 시간 구간에 100개 이상의 게시물을 작성해야만 배지를 획득할 수 있습니다.

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

참고로 멋진 배지 디자인이네요: CSS 스타일의 SVG인가요, 아니면 픽셀 기반 그래픽인가요?

8

아, 맞다! 그걸 깜빡했네요. 작동하네요. 너무 많은 부탁은 아닌지 모르겠지만, 사용자가 프로필에서 선택한 시간을 기준으로 그 시간 확인을 할 수 있을까요? 그렇지 않으면 유럽 외 지역 등에 사는 사람들을 위해 포럼이 “시계 지킴이” 배지로 가득 차 버릴 겁니다 :smile: 정말 감사합니다!

9

포토샵과 피그마로 제작했으며, CSS 스타일링은 사용하지 않았습니다. 디자인과 모든 배지를 마무리하는 대로 이 포럼에 URL을 게시하겠습니다.

10

사용자의 시간대를 고려하면 쿼리는 다음과 같습니다:

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

정말 감사합니다! :blush: