Calculating average time

Question

In my bank I have a table that has the fields entrada, saida, the two fields have the format D-MM-YYYY H:M:S, would like to make an average calculation.

Ex: The average waiting time is 30 min

Based on all the results on my chart, is it possible? I thought of some ways to do this, but I couldn’t even get a solution or a working logic.

I use Mysql. id | id_ent | entrada | saida | status

Answer 1

In Javascript (and in most languages, in fact), dates and times are represented in memory as the number of milliseconds from an arbitrary date-time. You can see this number when using the function getTime like Date, like this:

var agora = new Date();
agora.getTime();

So to know how long a call took, you can do the following:

var horaEntrada = foo;
var horaSaida = bar;

var tempoAtendimentoEmMilissegundos = bar.getTime() - foo.getTime();

Just swap fooand bar the actual times of entry and exit.

If you want to convert call time to other units of measurement, it’s easy:

var tempoAtendimentoEmSegundos = tempoAtendimentoEmMilissegundos / 1000;
var tempoAtendimentoEmMinutos = tempoAtendimentoEmSegundos / 60;

// etc., etc.

Finally, to get the average, just make a simple account. Accumulate all times in a sum, and divide the sum by the amount of calls.

var soma = 0;
for (let i = 0; i < tempos.length; i++) {
    soma += tempos[i];
}
var media = soma / tempos.length;

Answer 2

Additionally you can calculate the average directly in your query which returns the database data as follows:

SELECT AVG(TIME_TO_SEC(TIMEDIFF(t.saida, t.entrada))) / 60 AS espera_media
  FROM tabela t