凌晨1点至5点创建帖子的徽章

开发
1

我担心自己对这些徽章上瘾了 :smiling_face_with_three_hearts:
这个徽章将授予在凌晨 1 点到 5 点之间发布帖子的用户，并每日更新。
但遗憾的是，我不知道如何筛选这个时间段。如有任何帮助，不胜感激。

SELECT user_id, current_timestamp AS granted_at 
FROM badge_posts p  
WHERE p.created_at > CURRENT_DATE /* 如何在 '1AM' 和 '5AM' 之间筛选？*/ 
GROUP BY user_id 
HAVING count(*) >= 100

image
image412×190 8.51 KB

3 个赞
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

希望这能帮到您。

2 个赞
3

非常感谢您的帮助。我尝试了您的示例，但在预览中没有授予任何徽章。现在是上午 11:17，我在示例中将时间范围修改为 10:00 到 13:00 之间，并创建了一个演示主题和回复，但没有任何显示。我还尝试了以下查询：

SELECT 
    user_id, 
    current_timestamp AS 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:

1 个赞
4

数据库记录的时间戳为 GMT+0，因此您需要加上该时差以转换为您所在的时区。如果您告诉我您的时区，我可以为您调整。

3 个赞
5

好的。时区为欧洲/布鲁塞尔，GMT +01:00。

3 个赞
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
3 个赞
7

顺便提一下，很棒的徽章设计：它是用 CSS 样式化的 SVG，还是基于像素的图形？

3 个赞
8

天啊，你说得对，我忘了那件事。现在可以用了。如果不麻烦的话，能否根据用户在个人资料中设置的时间来进行时间检查？否则，论坛里会充满“守望者”徽章：smile:（针对欧洲以外地区的用户）。非常感谢！

9

本作品使用 Photoshop 和 Figma 制作，并非通过 CSS 样式实现。待设计完成及所有徽章就绪后，我将在本论坛发布 URL。

3 个赞
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
5 个赞
11

非常感谢！:blush:

1 个赞