How to return the date and time closest to that informed by the user

Asked

Viewed 1,358 times

2

I have a table with a list of places a truck has passed with the date/time of its entry and exit

+----+------------------+-----------+---------------+
| ID |   DATA E HORA    |   LOCAL   | ENTRADA/SAIDA |
+----+------------------+-----------+---------------+
|  1 | 28/01/2017 07:27 | FABRICA   | Entrada       |
|  2 | 28/01/2017 09:00 | FABRICA   | Saida         |
|  3 | 29/01/2017 00:02 | REVENDA 1 | Entrada       |
|  4 | 29/01/2017 04:00 | REVENDA 1 | Saida         |
|  5 | 29/01/2017 08:00 | REVENDA 2 | Entrada       |
|  6 | 29/01/2017 10:00 | REVENDA 2 | Saida         |
|  7 | 29/01/2017 20:00 | FABRICA   | Entrada       |
|  8 | 29/01/2017 23:00 | FABRICA   | Saida         |
+----+------------------+-----------+---------------+

I need to make a QUERY that with a date/time informed by the user, return where the truck was at that time.

Is there a function that returns the occurrence of nearest date/time from an informed in the QUERY? Is there a more efficient way to do it? How? Thanks for the help

mysql sql

  • What’s to happen if you’re told 29/01/2017 06:00 with the data from your example above?

  • It’s to find out where he was at that time, then I’ll calculate the time he was at that place

  • Good question, I never had a problem like that but I was curious about the solution!

  • Repeating @Bacco’s question, should return RESALE 1 or RESALE 2? Because both locations were 2h away from 29/01/2017 06:00, and in this table there is no "distance" field, so the calculation is done using the time.

  • I’m sorry, I didn’t realize that. If the value is not between an Input and Output it will be in Rota, I didn’t think of it when I "modeled" the Scupe bank. Is there any way around that?

  • @Luke has two good exits: one is to get out of this concept of a separate entrance from the exit, and put everything on two lines, and treat the separate route of the "presence". The other is to leave as is and display the date immediately before and the date immediately after, and when displaying, if it is equal to the location, it shows only one, if it is different it shows "between local1 and local2". Anyway, it needs [Dit] the question and make clear the rules, so that the answers can reflect the complete need. Be careful not to change to the point of totally invalidating the current responses.

Show 1 more comment

4 answers

2

You can sort the dates, filter the dates before the date given and limit the result to a record. Since you didn’t pass real information, such as table name and fields, I can’t write the exact SQL. But it will be something like this:

SELECT LOCAL FROM _TABELA WHERE DATA_FORNECIDA > DATA_E_HORA ORDER BY DATA_E_HORA LIMITE 1

  • I made a test here recreating this table of it and running your query and looking for where the vehicle was in '2017-01-29 00:03:00', showed FABRICA, when the desired answer would be RESALE 1

1

I believe this can help you :

    SELECT max(HORADOCAMINHAO), COD_CAMINHAO 
    FROM tabela
    WHERE
    DAT_SOLICITACAO < '2016-10-31 06:36:01.000'/*informe a data desejada*/ 
    and COD_CAMINHAO = 123
    GROUP BY COD_CAMINHAO

/*troque o que está dentro do() pela coluna que 
    informa a data, e informe as demais colunas desejadas 
    no GROUP BY */

  • In case this code is only from the records, each truck has a separate table

1

There is no INTERSECT for Mysql and it was necessary to emulate using UNION ALL. This SQL at first looks weird, but it is not complicated, and passes all tests, locating the correct location for each time. Follow mass for the test, logic and the tests themselves.

First a table was created that reproduces the one that was posted in the question.

CREATE TABLE `LISTA_LOCAIS` (
  `ID` int(11) NOT NULL,
  `DATA_HORA` timestamp NOT NULL,
  `LOCAL` varchar(100) NOT NULL,
  `ENTRADA_SAIDA` varchar(10) NOT NULL
)

Next we create a test mass identical to the table shown in the question.

INSERT INTO LISTA_LOCAIS(ID,DATA_HORA,LOCAL,ENTRADA_SAIDA) VALUES(1,'2017-01-28 07:27','FABRICA'   ,'Entrada');
INSERT INTO LISTA_LOCAIS(ID,DATA_HORA,LOCAL,ENTRADA_SAIDA) VALUES(2,'2017-01-28 09:00','FABRICA'   ,'Saida'  );
INSERT INTO LISTA_LOCAIS(ID,DATA_HORA,LOCAL,ENTRADA_SAIDA) VALUES(3,'2017-01-29 00:02','REVENDA 1' ,'Entrada');
INSERT INTO LISTA_LOCAIS(ID,DATA_HORA,LOCAL,ENTRADA_SAIDA) VALUES(4,'2017-01-29 04:00','REVENDA 1' ,'Saida'  );
INSERT INTO LISTA_LOCAIS(ID,DATA_HORA,LOCAL,ENTRADA_SAIDA) VALUES(5,'2017-01-29 08:00','REVENDA 2' ,'Entrada');
INSERT INTO LISTA_LOCAIS(ID,DATA_HORA,LOCAL,ENTRADA_SAIDA) VALUES(6,'2017-01-29 10:00','REVENDA 2' ,'Saida'  );
INSERT INTO LISTA_LOCAIS(ID,DATA_HORA,LOCAL,ENTRADA_SAIDA) VALUES(7,'2017-01-29 20:00','FABRICA'   ,'Entrada');
INSERT INTO LISTA_LOCAIS(ID,DATA_HORA,LOCAL,ENTRADA_SAIDA) VALUES(8,'2017-01-29 23:00','FABRICA'   ,'Saida'  );

Follow SQL now to search for the location at a certain time:

SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND 'SUA_TIMESTAMP_PESQUISADA' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND 'SUA_TIMESTAMP_PESQUISADA' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2

Explanation: First it takes a part that marries with the entrance. Then it takes a part that marries with the exit. It makes the intersection of the two. Notice the drawing of the decreasing ordering to the input and ascending ordering to the output to limit well the records of that intersection.

Follow the tests:

-- TESTE 1:  REVENDA 1 em 2017-01-29 00:03:00
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-29 00:03:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-29 00:03:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 2: FABRICA em 2017-01-28 08:00
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-28 08:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-28 08:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 3: EM LUGAR NENHUM / CAMINHÃO EM ROTA em 2017-01-28 12:00 - não tem a entrada do local para esse período
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-28 12:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-28 12:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 4: REVENDA 2 em 2017-01-29 08:00
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-29 08:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-29 08:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 5: REVENDA 2 em 2017-01-29 09:00
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-29 09:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-29 09:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 6: REVENDA 2 em 2017-01-29 10:00
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-29 10:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-29 10:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 7: LUGAR NENHUM / CAMINHÃO EM ROTA em 2017-01-29 16:00
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-29 16:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-29 16:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 8: FABRICA em 2017-01-29 20:00
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-29 20:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-29 20:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 9: FABRICA em 2017-01-29 23:00
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-29 23:00' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-29 23:00' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2


-- TESTE 10: LUGAR NENHUM  / CAMINHÃO EM ROTA  em 2017-01-29 23:10
SELECT LOCAL from (
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Entrada' AND '2017-01-29 23:10' >= DATA_HORA ORDER BY DATA_HORA DESC LIMIT 2)
  UNION ALL 
  (SELECT LOCAL FROM LISTA_LOCAIS WHERE
ENTRADA_SAIDA='Saida' AND '2017-01-29 23:10' <= DATA_HORA ORDER BY DATA_HORA ASC LIMIT 2)
) AS TABELA_UNIFICADA  GROUP BY LOCAL HAVING count(*) >= 2

0

With this select I can select the nearest location of that truck at any given time.

SELECT LOCAL, min( abs( UNIX_TIMESTAMP(DATA_HORA) -  UNIX_TIMESTAMP(STR_TO_DATE('29-01-2017 05:00', '%d-%m-%Y %H:%i:%s')))) AS PROVAVEL from lista_locais
GROUP BY LOCAL 
ORDER BY PROVAVEL LIMIT 1

inserir a descrição da imagem aqui

As 5 hours of the day 29/01/2017 he is closest to resale 1.

SELECT LOCAL, min( abs( UNIX_TIMESTAMP(DATA_HORA) -  UNIX_TIMESTAMP(STR_TO_DATE('29-01-2017 08:30', '%d-%m-%Y %H:%i:%s')))) AS PROVAVEL from lista_locais
GROUP BY LOCAL 
ORDER BY PROVAVEL LIMIT 1

inserir a descrição da imagem aqui

And at 8:30 on the same day he’s at 2.

Browser other questions tagged mysql sql

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