Calculating Average Time of Answer between Receiver and Sender in MySQL

===========================================================

In this article, we will explore how to calculate the average time of answer between a receiver and sender in a conversation. We will provide two methods for achieving this: one using MySQL 8’s built-in functions, and another for MySQL versions below 8.

Understanding the Problem

We have a table called message_conversation with columns id, conversation_id, receiver_id, sender_id, message, and created_at. The task is to find the average time between messages sent by a specific sender (sender_id = 2) in the same conversation.

MySQL 8 Method

The first method we will discuss uses MySQL 8’s built-in functions: UNIX_TIMESTAMP() to convert date to seconds, and SEC_TO_TIME() to format it as time.

Table Creation

To demonstrate this method, let’s create a table called message_conversation:

create table message_conversation (
  id int,
  conversation_id int,
  receiver_id int,
  sender_id int,
  message char(2),
  created_at date
);

Inserting Data

Insert some sample data into the table:

insert into message_conversation values
(1,   1,                   1,             2,            'A',         '2018-08-01'),
(2,   1,                   2,             1,            'B',         '2018-08-02');

Calculating Average Time of Answer

Now, let’s calculate the average time of answer using a Common Table Expression (CTE) to find the difference in seconds between consecutive messages:

with reply_time as
(select
  sender_id,
  conversation_id,
  UNIX_TIMESTAMP(created_at) 
  - UNIX_TIMESTAMP( lag(created_at) over 
                      (partition by conversation_id 
             order by created_at)
  ) as diff_seconds
  from message_conversation
)
select 
  SEC_TO_TIME(avg(coalesce(diff_seconds,0))) time_diff
from reply_time
where coalesce(diff_seconds,0) > 0
and sender_id = 1 ;

This query calculates the difference in seconds between each pair of consecutive messages for the specified sender_id. The average of these differences is then converted to a human-readable time format using SEC_TO_TIME().

Result

The result should be:

| time_diff     |
| :------------ |
| 24:00:00.0000 |

MySQL 5 and Below Method

For older versions of MySQL (below version 8), we need to use a different approach. In this case, we will use the TIMESTAMPDIFF() function with the SECOND unit.

Table Creation

Create the same table as before:

create table message_conversation (
  id int,
  conversation_id int,
  receiver_id int,
  sender_id int,
  message char(2),
  created_at date
);

Inserting Data

Insert the same sample data:

insert into message_conversation values
(1,   1,                   1,             2,            'A',         '2018-08-01'),
(2,   1,                   2,             1,            'B',         '2018-08-02');

Calculating Average Time of Answer

Now, let’s calculate the average time of answer:

set @prev = '3000-01-01 00:00:00';
set @conv = 0;
select
  sec_to_time(avg(seconds_diff)) average_difference
from
(select
  case when conversation_id = @conv then
     TIMESTAMPDIFF(
    SECOND,
    @prev,
    created_at
    ) end seconds_diff,
  @conv := conversation_id,
  @prev := created_at
from message_conversation) a
where coalesce(seconds_diff,0) > 0;

This query uses the TIMESTAMPDIFF() function to calculate the difference in seconds between each pair of consecutive messages for the specified sender_id. The average of these differences is then converted to a human-readable time format using sec_to_time().

Result

The result should be:

| average_difference |
| :----------------- |
| 24:00:00.0000      |

Conclusion

In this article, we have demonstrated how to calculate the average time of answer between a receiver and sender in a conversation using MySQL. We provided two methods for achieving this: one using MySQL 8’s built-in functions, and another for older versions of MySQL below version 8.

By following these steps, you should be able to calculate the average time of answer for any sender_id in your own database.

Last modified on 2025-02-13