Calculate SQL mean with hour field

-1

Good morning, I am having trouble resolving the select below. I have the select below that returns me the amount of hours of a given event and apparently returns in varchar. What I'm having difficulty is to take this amount of hours and divide by the other Total Record field. I have tried to do the conversions without success.

Follow print

    
asked by anonymous 05.07.2018 / 16:41

1 answer

4

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

    
05.07.2018 / 17:09