Rule for selecting records based on date and time

Question

I am developing an online betting system and when selecting the games registered in the system I need to select only the games from the current day onwards, additionally I need to make sure not to select the games that have already started, that is, those in which the current time is greater than the time of registration.

So far I can select the records from today onwards by date, but I couldn’t make the time condition. I’ll leave the query I currently have below.

SELECT GROUP_CONCAT(timee.nome_time ORDER BY timee.nome_time SEPARATOR ' X ') AS nome_time, 
partida.id, DATE_FORMAT(partida.data_hora, '%d/%m/%Y %H:%i') AS data_hora, 
partida.tb_cotacao_id
FROM tb_partida AS partida, tb_time AS timee, tb_partida_time AS partidaTime 
WHERE (partida.id = tb_partida_id && timee.id = tb_time_id)
AND (partida.flag_ativo = 1 AND partida.flag_cancelado <> 1 AND partida.flag_finalizado <> 1) 
AND (date(partida.data_hora) >= date(now()) AND (TIME_TO_SEC(TIMEDIFF(now(), partida.data_hora)) > 600))
AND partida.tb_campeonato_id = 11 
GROUP BY partida.id

Answer 1

By the structure you are using the DATETIME in partida.data_hora.

If you use:

date(partida.data_hora) >= date(now())

Is ensuring that the year, month and day will be longer and equal to the current one, but the time in both does not enter the comparison, in other words:

SELECT DATE('2017-12-31 01:02:03');
// Resultado : 2017-12-31

You can simply remove the DATE(), that way it would be:

partida.data_hora >= now()

That would make me compare the 01:02:03, thus achieving what you desire.

For example:

Using without the date(), the solution:

SELECT '2017-01-02 01:02:03' >= '2017-01-02 01:02:01'
// Resposta: 1

SELECT '2017-01-02 01:02:03' >= '2017-01-02 01:02:04'
// Resposta: 0

While using date():

SELECT DATE('2017-01-02 01:02:03') >= DATE('2017-01-02 01:02:01')
// Resposta: 1

SELECT DATE('2017-01-02 01:02:03') >= DATE('2017-01-02 01:02:04')
// Resposta: 1

Extra:

If I understand SQL correctly, when you use >= now() it makes no sense to use the (TIME_TO_SEC(TIMEDIFF(now(), partida.data_hora)) > 600, that would be redundant.

You could replace:

(date(partida.data_hora) >= date(now()) AND (TIME_TO_SEC(TIMEDIFF(now(), partida.data_hora)) > 600))

For:

(partida.data_hora >= (now() + INTERVAL 10 MINUTE))

This would ensure that partida.data_hora is greater than the current date summed with 10 minutes, which would solve the two problems.