Time to response

Data & reporting
1

Hi Discourse !

I was wondering how you calculate the time to first response in your reports.
Based on what collection from your DB ?

Thank you for your time :slight_smile:
Jeanne

2

Time to first response is calculated by subtracting the value of the topic’s created_at field from the value of the created_at field of the topic’s first reply. The first reply is the first post in a topic that has a post_number greater than 1 and has a post_type of 1. (a post_type of 1 is used to indicate that a post is a ‘regular’ post, and not a small action, moderator action, or whisper post.)

3 Likes
3

@simon thanks for your answer.
I am using the data explorer plugin. Do you know in what table I can find this field_first reply ?
I search for it but in vain :frowning:

Many thanks

Jeanne

4

There isn’t a field in the database for this. Discourse uses a method that’s called on the Topic class to calculate the time to first response. You can see the code for it here: discourse/app/models/topic.rb at main · discourse/discourse · GitHub.

This is approximately the SQL query that is generated by the time_to_first_response method. It can be run as a Data Explorer query:

SELECT t.id AS topic_id, AVG(t.hours)::float AS hours, t.created_at
FROM (
  SELECT t.id, t.created_at::date AS created_at, EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at)::float / 3600.0 AS hours
  FROM topics t
  LEFT JOIN posts p ON p.topic_id = t.id
  WHERE t.archetype = 'regular'
  AND t.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND p.post_number > 1
  AND p.user_id != t.user_id
  AND p.post_type = 1
  GROUP BY t.id
) t
GROUP BY t.created_at, t.id
ORDER BY hours DESC
5 Likes
6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.