Record selection rule based on date and time

Navigation
2

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, ie those in the current time is greater than the time of the record.

So far I can select the records from now on by date, but I could not 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
    
asked by anonymous 21.01.2017 / 17:59

1 answer

0

By structure you are using DATETIME in partida.data_hora .

If you use: 

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

You are ensuring that the year, month, and day will be greater than and equal to the current, but the time in both does not go into the comparison, in other words: 

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

You can simply remove DATE() , so it would look like: 

partida.data_hora >= now()

This would cause you to also compare 01:02:03 , thus achieving what you want.

For example:

Using without 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 understood SQL correctly, when you use >= now() it does not make sense to use (TIME_TO_SEC(TIMEDIFF(now(), partida.data_hora)) > 600 , this would be redundant.

You could replace: 

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

By: 

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

This would ensure that partida.data_hora is necessarily greater than the current date plus 10 minutes, which would solve both problems.

    
21.01.2017 / 23:39
Upload image after selected item Session Difference in Java