Assuming you have something like:
CREATE TABLE tb_evento
(
id INTEGER PRIMARY KEY,
dt_inicial DATETIME,
dt_final DATETIME
);
INSERT INTO tb_evento (id, dt_inicial, dt_final) VALUES (1, '2016-05-01 23:00', '2016-05-02 03:00');
INSERT INTO tb_evento (id, dt_inicial, dt_final) VALUES (2, '2016-11-01 21:15', '2016-11-01 23:30');
INSERT INTO tb_evento (id, dt_inicial, dt_final) VALUES (3, '2016-12-24 00:00', '2016-12-25 12:00');
INSERT INTO tb_evento (id, dt_inicial, dt_final) VALUES (4, '2016-02-01 23:00', '2016-02-02 23:00');
INSERT INTO tb_evento (id, dt_inicial, dt_final) VALUES (5, '2016-03-15 08:30', '2016-03-15 16:45');
INSERT INTO tb_evento (id, dt_inicial, dt_final) VALUES (6, '2016-08-01 20:00', '2016-08-02 01:30');
You can use the TIMESTAMPDIFF()
function to calculate the interval in minutes between two dates, see.
SELECT
id,
dt_inicial,
dt_final,
(TIMESTAMPDIFF( MINUTE, dt_inicial, dt_final ) / 60) AS duracao
FROM
tb_evento;
Output:
| id | dt_inicial | dt_final | duracao |
|----|----------------------|----------------------|---------|
| 1 | 2016-05-01T23:00:00Z | 2016-05-02T03:00:00Z | 4 |
| 2 | 2016-11-01T21:15:00Z | 2016-11-01T23:30:00Z | 2.25 |
| 3 | 2016-12-24T00:00:00Z | 2016-12-25T12:00:00Z | 36 |
| 4 | 2016-02-01T23:00:00Z | 2016-02-02T23:00:00Z | 24 |
| 5 | 2016-03-15T08:30:00Z | 2016-03-15T16:45:00Z | 8.25 |
| 6 | 2016-08-01T20:00:00Z | 2016-08-02T01:30:00Z | 5.5 |
To calculate how many hours an event lasts on average, you can use the aggregation function AVG()
, see:
SELECT
COUNT(1) AS qtd_eventos,
AVG( TIMESTAMPDIFF( MINUTE, dt_inicial, dt_final ) / 60 ) AS duracao_media
FROM
tb_evento;
Output:
| qtd_eventos | duracao_media |
|-------------|---------------|
| 6 | 13.33333333 |
See working in SQLFiddle