Olá,
Tenho um banco de dados SQL do Discourse. Preciso encontrar estatísticas de usuários para um carimbo de data/hora específico.
Por exemplo: preciso descobrir quantas postagens um usuário fez.
Se eu extrair esses dados de user_stats, ele fornecerá um número desde que o usuário se juntou ao fórum até agora, mas preciso obter isso para um período de tempo específico.
Alguma ideia?
You will need to generate the statistics yourself using a new database query. For example, to get a post count for the last 30 days
SELECT count(*) from posts
WHERE user_id=1
AND created_at > CURRENT_TIMESTAMP - INTERVAL '30 days'
This topic has a lot of example queries you can use as a starting point
Thanks @david 
But what if i want to give a proper time instead of days like if i want to find between 2020-2-2 00:00:00 and 2020-3-1 00:00:00 ?
If i do SELECT count(*) from posts WHERE user_id=16 AND created_at > CURRENT_TIMESTAMP - INTERVAL '30 days'
I got a number but when i look into my stats its less than that number.
If you see this. I got 430 from the database but its actually 308 ?
The count shown in the UI is only for public, undeleted, posts. The database count is every post, including deleted posts, PMs, and private categories.
But what if i want to get that UI stat with in a time period?
You will need to apply similar filters. Something like this:
SELECT count(*) FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE p.user_id=23968
AND p.deleted_at IS NULL
AND NOT COALESCE(p.hidden, 't')
AND p.post_type = 1
AND t.deleted_at IS NULL
AND COALESCE(t.visible, 't')
AND t.archetype <> 'private_message'
AND p.user_id > 0
AND p.post_number > 1