Search date and time range PHP MYSQL

Asked

Viewed 810 times

-1

Assuming there is an ex date range: 2019-02-22 and 2019-02-26 I need to search the database by date and time (I need to extract the value by specific date and time, for example only from 05:00 to 13:30). I’d like to do that:

  "SELECT tempo FROM manutencao WHERE data BETWEEN '2019-02-22 05:00:00' AND '2019-02-22 13:30:00'";
Guarda o resultado em uma variável;
    "SELECT tempo FROM manutencao WHERE data BETWEEN '2019-02-23 05:00:00' AND '2019-02-23 13:30:00'";
Soma o resultado com a variável anterior
    "SELECT tempo FROM manutencao WHERE data BETWEEN '2019-02-24 05:00:00' AND '2019-02-24 13:30:00'";
Soma o resultado com a variável anterior
    "SELECT tempo FROM manutencao WHERE data BETWEEN '2019-02-25 05:00:00' AND '2019-02-25 13:30:00'";
Soma o resultado com a variável anterior
    "SELECT tempo FROM manutencao WHERE data BETWEEN '2019-02-26 05:00:00' AND '2019-02-26 13:30:00'";
Soma o resultado com a variável anterior

do I have to do a FOR or a WHILE for him to create this loop of repetition?

I tried to do all this in one line from sql but without success.

php mysql date logic

1 answer

1

I’m not sure I understand the question, but I think you could use something like:

SELECT SUM(tempo) from manutencao where DATE(Data) BETWEEN DATE('{$data_inicial}') AND DATE('{$data_final}') AND TIME(Data) BETWEEN time('05:00:00') AND TIME('13:30:00');

  • Thanks for the return @Bruno, it would work, but there where you put SUM(time), is not a time field, what you would have to do is the following account, there is the field hora_parada and hora_retorno, the fields are DATETIME, assuming that a machine stopped day '2019-02-22 09:00' and returned day '2019-02-22 15:00', would be 04:30 of machine stopped on 22/02 (from 05:00 to 13:30); And assuming that the same machine stopped day '2019-02-23 10:00' and returned day '2019-02-23 11:00', would be 01:00 of machine stopped on 23/02 (from 05:00 to 13:30); so the "sum" of the stops would be 05:30 ?

Browser other questions tagged php mysql date logic

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