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

Question

I’m having trouble picking up the current time, in case I’m using CURTIME(), need to fetch the results that the current time, is between the value(time) of the column initial time and of final time, tried in the following ways, however, without success:

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 CURTIME() >= ProgramaHorario.horario_inicio AND CURTIME() <= ProgramaHorario.horario_fim

and

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 CURTIME() BETWEEN ProgramaHorario.horario_inicio AND ProgramaHorario.horario_fim

It is possible to use between? and the most suitable to work with the current time, is CURTIME() even?

Answer 1

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, provided that the types of the columns are the same. In some cases, you can use a CAST, even if implicit, in this way:

-- 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 take is when mixing timestamp with only team. In this case, it pays to extract the necessary part with the functions TIME or DATE if you want to disregard the date:

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

Beware when it comes to cases where the timetable involves two different days as between 23h00 today and 02h00 tomorrow, for example. See the solution below.

There are several other possible combinations of CAST and selection of the desired part, just you isolate what is most convenient for your case.

Applying to the practical case:

Here we have a query that applies the above concepts, and that takes into account both the times on the same day and two days in a row:

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

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

If the initial time is 12:00 and the end time is 14:00, and the current time is 13:00:

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 initial 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

Thus, with a query relatively simple, we solve all cases involving up to 24 hours, without having to use dates.

Answer 2

Now it worked, really the logic was wrong. As suggested in a comment in the previous reply, I was reversing the initial and final time values.

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();