View all topics in category filter by date created


#1

I need to look through all topics that were created in a specific category within a specific date range. Any way to do that?


#2

Do you have Data Explorer installed? If so, this query by @Simon_Cossar will return all topics for a specific month, sorted by category. Would that do the trick?

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)

SELECT
    t.id as topic_id,
    t.category_id
FROM topics t
RIGHT JOIN query_period qp
    ON t.created_at >= qp.period_start
        AND t.created_at <= qp.period_end
WHERE t.user_id > 0
    AND t.category_id IS NOT NULL
ORDER BY t.category_id, t.created_at DESC

(Note that we haven’t tested this yet so let us know if you have feedback).


(Jay Pfaffman) #3

If you click “options” after clicking the search :mag: icon, you can search by date, category, and a bunch of other stuff.