Calculate SQL mean with time field

Asked

Viewed 1,763 times

-1

Good morning Everyone, I’m having trouble solving the select below. I have the select below that returns me the amount of hours of a given event and by the return visa in varchar. What I’m having trouble with is taking this amount of hours and dividing it by the other Total Record field. I tried to make the conversions but without success.

Follows print

sql datetime
  • 5

    Avoid placing images with the code. Can pf include it in text? It is easier to validate the question.

  • 1

    What is the database ?

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 function TIMESTAMPDIFF() to calculate the interval in minutes between two dates, see only.

SELECT
  id,
  dt_inicial,
  dt_final,
  (TIMESTAMPDIFF( MINUTE, dt_inicial, dt_final ) / 60) AS duracao
FROM
  tb_evento;

Exit:

| 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(), look at you: 

SELECT
  COUNT(1) AS qtd_eventos,
  AVG( TIMESTAMPDIFF( MINUTE, dt_inicial, dt_final ) / 60 ) AS duracao_media
FROM
  tb_evento;

Exit:

| qtd_eventos | duracao_media |
|-------------|---------------|
|           6 |   13.33333333 |

See working on Sqlfiddle

Browser other questions tagged sql datetime

You are not signed in. Login or sign up in order to post.