Nostr Web Client

UTCを日本時間考慮したら4/2の分はこんな感じ？

SELECT event_pubkey, COUNT(*) AS event_count FROM events WHERE `event_created_at` >= "2023-04-01 15:00" and `event_created_at` < "2023-04-02 15:00" AND event_kind=1 GROUP BY event_pubkey ORDER BY event_count DESC;

mattn 2y ago

もし PostgreSQL なら event_create_at の部分は to_timestamp(event_created_at) が良さそう。

Reply to this note

Please Login to reply.

Discussion

七海灯 2y ago

SELECT encode(event_pubkey::bytea, 'hex'), COUNT(*) AS event_count FROM events WHERE to_timestamp(event_created_at) >= TO_TIMESTAMP('2023-04-01 15:00:00', 'YYYY/MM/DD HH24:MI:SS') and to_timestamp(event_created_at) < TO_TIMESTAMP('2023-04-02 15:00:00', 'YYYY/MM/DD HH24:MI:SS') AND event_kind=1 GROUP BY event_pubkey ORDER BY event_count DESC;

で抽出できそうです。

有難うございます!