Média entre horários datetime

[raelpsf 0]

Pessoal, tenho a seguinte dúvida..

 

Tenho uma tabela onde vai gerando log conforme o cadastro de informações no sistema..

 

Essas informações são cadastradas e gera uma coluna datetime, para saber o dia e a hora que foi cadastrada.

 

Porém, eu gostaria de tirar uma média diária, de quanto tempo levou entre um cadastro e outro..

 

Ex. hoje teve 04 cadastros..

 

09:00

 

09:10

 

09:20

 

09:30

 

Assim, a pesquisa me retornaria a resposta de 10 min de média entre um cadastro e outro.

 

Como fazer isso numa pesquisa direto no banco?

 

Tabela: interacoes

coluna: data

 

De preferencia, com a opção de eu escolher o dia da consulta (between), não somente para "hoje".

 

Muito Obrigado.

Dúvida adicional.. tem como o sistema levar em consideração a 01 hora de almoço do funcionário.. tipo..

 

Soma as horas, desconta 01 hora, e divide pelo número de entradas.. algo assim?

[Motta 645]

A chave deve ser um id sequencial.

Faça um join da tabela com ela mesma de forma que id=(id-1), ou seja um registro será ligadonao anterior.

Calcule a diferença em minutos.

Calcule a média desta diferença.

[raelpsf 0]

Ai que tá.. não conheço as entradas do mysql pra fazer esses cálculos.. eu estou aprendendo sobre mysql e a maioria dos códigos que uso, são adaptações que faço de outros que já conheço..

 

Mas esse tipo de pesquisa nunca fiz.. se alguém puder me ajudar com a construção deste select eu ia agradecer.. não conheço as variáveis..

 

E para constar.. mota.. a planilha tem um id sequencial sim.. deixei o nome de "id" msm..

 

Também só para constar.. são cerca de 100 registros por dia.. então as média vai ter que levar isso tudo em conta.

[Motta 645]

Publica a estrutura da tabela.

[raelpsf 0]

 

Abraço.

[Motta 645]

obter a data

select data
from tabela

ligando a anterior

 

select *
from tabela t1,tabela t2
where t1.id  = (t2.id-1)

dif. entre datas

 

select (t1.data - t2.data) 
from tabela t1,tabela t2
where t1.id  = (t2.id-1)

 

A diferença entre datas é em dias logo se multiplicar por 24 e por 60 terá em munutos

 

 

select 24 * 60 * (t1.data - t2.data) 
from tabela t1,tabela t2
where t1.id  = (t2.id-1)

média desta história

 

select avg (24 * 60 * (t1.data - t2.data) )
from tabela t1,tabela t2
where t1.id  = (t2.id-1)

[raelpsf 0]

Só pra ver se eu entendi.. as datas estão todas na tabela interacoes.. então ficaria..

 

 

 

 

select avg (24 * 60 * (interacoes.data - interacoes.data) ) from interacoes,interacoes where interacoes.id  = (interacoes.id-1)

 

???

[Motta 645]

Não, como a tabela e referenciada duas vezes é preciso de um aliais

select avg(24 * 60 * (i2.data - i1.data)) media
from interacoes i1,interacoes i2
where i1.id  = (i2.id-1)

 

 

[raelpsf 0]

Entendi...

 

O resultado foi esse..

 

416919732.9735487000

 

Acredito pq ele puxou os vários meses que o banco tem..

 

Como faço para limitar para a data X apenas?

[Motta 645]

Não, fiz bobagem ...

Tem de dividir pois o resultado é em dias.

 

select avg(((i2.data - i1.data)/(24 * 60)) media 
from interacoes i1,interacoes i2 
where i1.id  = (i2.id-1)

 

Como faço para limitar para a data X apenas?

 

Condição no WHERE ...

 

30 últimos dias (por exemplo)

 

select avg(((i2.data - i1.data)/(24 * 60)) media 
from interacoes i1,interacoes i2 
where i1.id  = (i2.id-1) 
and i1.data >= (now() -30)

Veja o manual do MySql para detalhes do uso do SQL.

 

 

 

[raelpsf 0]

Cara primeiro deu erro de sintax, ai eu tirei um dos ( , ali de depois do avg, ai foi.. mas tá retornando NULL.. o que pode ser?

[Motta 645]

Alguma data nula talvez, acrescente um

 

and data i1.data is not null
and data i2.data is not null

a query

 

publica como ficou a query

[raelpsf 0]

Fiz assim... mas retornou null igual..

 

O estranho é que é impossível ter data nula, pq a data é inserida no momento do cadastro da informação.. ela pega a hora e data do ato do cadastro..

 

SELECT avg( (
i2.data - i1.data
) / ( 24 *60 ) ) media
FROM interacoes i1, interacoes i2
WHERE i1.id = ( i2.id -1 )
AND i1.data >= ( now( ) -30 )
AND i1.data IS NOT NULL
AND i2.data IS NOT NULL 

[Motta 645]

Corrigindo ...

é para multiplicar mesmo , pois

1dia = 24 horas

1 h = 60 minutos

1 dia = 1440 minutos

SELECT avg((24 * 60 * (i2.data - i1.data))) media 
FROM interacoes i1, interacoes i2 
WHERE i1.id = ( i2.id -1 ) 
AND i1.data >= ( now( ) -30 ) 
AND i1.data IS NOT NULL 
AND i2.data IS NOT NULL 

1) o que isto retorna ?

SELECT i1.id,i1.data,(24 * 60 * (i2.data - i1.data)) x,i2.id,i2.data
FROM interacoes i1, interacoes i2 
WHERE i1.id = ( i2.id -1 ) 
AND i1.data >= ( now( ) -30 ) 
AND i1.data IS NOT NULL 
AND i2.data IS NOT NULL 

 

[raelpsf 0]

O normal retornou null..

 

O que tu botou no 01) retornou..

 

MySQL retornou um conjunto vazio (ex. zero registros). ( Consulta levou 0.3722 segundos )

[Motta 645]

se isto

SELECT i1.id,i1.data,(24 * 60 * (i2.data - i1.data)) x,i2.id,i2.data 
FROM interacoes i1, interacoes i2 
WHERE i1.id = ( i2.id -1 ) 
AND i1.data >= DATE_SUB(data,INTERVAL 31 DAY)
AND i1.data IS NOT NULL 
AND i2.data IS NOT NULL 

retornar linhas

 

rode ...

 

SELECT avg(24 * 60 * (i2.data - i1.data)) x 
FROM interacoes i1, interacoes i2 
WHERE i1.id = ( i2.id -1 ) 
AND i1.data >= DATE_SUB(data,INTERVAL 31 DAY)
AND i1.data IS NOT NULL 
AND i2.data IS NOT NULL 

 

 

[raelpsf 0]

Nas duas opções retorna...

 

#1052 - Column 'data' in where clause is ambiguous

[Motta 645]

SELECT avg(24 * 60 * (i2.data - i1.data)) x 
FROM interacoes i1, interacoes i2 
WHERE i1.id = ( i2.id -1 ) 
AND i1.data >= DATE_SUB(NOW(),INTERVAL 31 DAY) 
AND i1.data IS NOT NULL 
AND i2.data IS NOT NULL 

 

 

[raelpsf 0]

x = 34186317.6068376100

[Motta 645]

Muito alto, estranho ... tente com DATEDIFF

 

SELECT avg(24 * 60 * (DATEDIFF(i2.data,i1.data))) x 
FROM interacoes i1, interacoes i2 
WHERE i1.id = ( i2.id -1 ) 
AND i1.data >= DATE_SUB(NOW(),INTERVAL 31 DAY) 
AND i1.data IS NOT NULL 
AND i2.data IS NOT NULL