Find records between a range of seconds

Question

I have a table where the date field contains full date (with seconds), I need with a given date, search all records with 10 seconds above and 10 seconds below, the query I tried to do is this:

SELECT DATA FROM deals WHERE direcao = 'in' AND DATA BETWEEN CAST(ADDTIME('2019-04-04 10:45:00', '10') AS DATETIME) AND CAST(SUBTIME('2019-04-04 10:45:00', '10') AS DATETIME);

But for some reason it does not return anything, I have some lines within this range of 20 seconds, but it does not return anything, I have the impression that the problem is the type of the data.

Thanks in advance.

Answer 1

You reversed the ADDTIME and SUBTIME in the BETWEEN, in the way that you are searching for all the records of 2019-04-04 10:45:10 until 2019-04-04 10:44:50. That’s why your query is not returning anything:

SELECT `DATA`
FROM deals
WHERE `DATA` BETWEEN SUBTIME('2019-05-04 17:36:15', 10) AND ADDTIME('2019-05-04 17:36:15', 10);

NOTE: It is not necessary to do the CAST.

See working on Sqlfiddle.