Time between (between) "start time" and "end time"

Summary of MySQL documentation:

CURRENT_DATE() and CURRENT_DATE are synonymous with CURDATE()

mysql> SELECT CURDATE();        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;    -> 20080613

CURRENT_TIME() and CURRENT_TIME are synonymous with CURTIME()

mysql> SELECT CURTIME();        -> '23:50:26'
mysql> SELECT CURTIME() + 0;    -> 235026.000000

CURRENT_TIMESTAMP() and CURRENT_TIMESTAMP are synonymous with NOW()

mysql> SELECT NOW();            -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;        -> 20071215235026.000000

The use of BETWEEN is perfectly feasible in these cases, as long as the column types are the same. In some cases, you can use a CAST, even if implicit, like this:

-- implícito e numérico
SELECT * FROM tabela WHERE CURTIME() + 0 BETWEEN hora1 + 0 AND hora2 + 0
-- explícito e horário apenas 
SELECT * FROM tabela WHERE CURTIME() BETWEEN CAST( hora1 AS TIME ) AND CAST( hora2 AS TIME)

The care you need to have is when mixing timestamp with just time . In this case, it is worthwhile to extract the necessary part with the TIME or DATE functions if you want to disregard the date:

SELECT * FROM tabela WHERE CURTIME() BETWEEN TIME( dataHora1 ) AND TIME( dataHora2 )

  

Watch out for cases where the time period involves two different days as between 23:00 today and 02:00 tomorrow, for example. See the solution below.

There are several other possible combinations of CAST and selection of the part you want, you simply isolate what is most convenient for you.

Applying to the case study:

Here is a query that applies the above concepts, which takes into account both the same-day and two-day schedules:

SELECT * FROM tabela WHERE
   ( CURTIME() BETWEEN LEAST( hora1, hora2 ) AND GREATEST( hora1, hora2 ) )
   XOR
   ( hora2 > hora1 )

Explanation: BETWEEN will return true , if the time is between the lowest of the times and the longest (automatically sorted by LEAST and GREATEST ). However, in a case where the end time is less than the initial time, it means that we want a number that is not in this range, see example:

If the starting time is 12h00 and the end time is 2h00, and the current time is 13h00:

a condição   CURTIME() BETWEEN LEAST( hora1, hora2 ) AND GREATEST( hora1, hora2 )
equivale a   13:00:00 BETWEEN LEAST( 12:00:00, 14:00:00 ) AND GREATEST( 12:00:00, 14:00:00 )
equivale a   13:00:00 BETWEEN 12:00:00 AND 14:00:00
resulta em   true

a condição   hora1 > hora2
resulta em   false

a condição   true XOR false
resulta em   true

However, if the starting time is 23h00 and the end time is 02h00, and the program 01h00:

a condição   CURTIME() BETWEEN LEAST( hora1, hora2 ) AND GREATEST( hora1, hora2 )
equivale a   01:00:00 BETWEEN LEAST( 23:00:00, 02:00:00 ) AND GREATEST( 23:00:00, 02:00:00 )
equivale a   01:00:00 BETWEEN 02:00:00 AND 23:00:00
resulta em   false (deu false por que as horas estão invertidas, mas está dentro do horário)

a condição   hora1 > hora2
resulta em   true

a condição   false XOR true (aqui corrigimos a inversão das horas)
resulta em   true

So, with a relatively simple query , we resolve all cases involving up to 24 hours, without using dates.

Now it worked, the logic was wrong. As suggested in a comment in the previous answer, I was reversing the values of the start and end times.

SELECT Programa.name, ProgramaHorario.horario_inicio, ProgramaHorario.horario_fim FROM programas AS Programa
INNER JOIN programa_horarios as ProgramaHorario ON ProgramaHorario.programa_id = Programa.id
WHERE ProgramaHorario.horario_inicio >= CURTIME() AND ProgramaHorario.horario_fim <= CURTIME();