SQL query to get the queue of all the PMs in the system

Support
3

Here is the query to get all PMs for last 7 days.

SELECT p.user_id, p.topic_id, p.post_number, p.raw, p.created_at::date
FROM posts p
LEFT JOIN topics t on t.id = p.topic_id
WHERE t.archetype = 'private_message'
  AND t.title <> 'YOUR_WELCOME_MESSAGE_TITLE'
  AND p.created_at::date > now()::date - 8
ORDER BY p.created_at DESC

  • Replace YOUR_WELCOME_MESSAGE_TITLE by the exact title of your welcome message, so that all welcome messages generated automatically are excluded (is there a better way to exclude automatically generated messages?)

  • Adjust how many days to look back for by changing the number in now()::date - 8.
    8 means 7 days;
    7 means 6 days
    …and so on.

4 Likes