How to check events between 2 dates in Mysql?

Question

Knowing I have a table called eventos, and this table has the columns id, titulo, inicio and fim. The columns inicio and fim are of the type timestamp, where the date and time of the start and end of a certain event is recorded. When making a select in this table, how do I catch the events of a specific date between the column date beginning and the column end?

Example: Assuming I have a record in this table with the column beginning being of value 2014-02-01 13:00:00 and the column end with the value 2014-02-05 22:30:00. It is an interval of 4 days, and their respective hours. How could I catch this record on a specific day using the following SQL below?

SELECT titulo
FROM eventos
WHERE inicio <= '2014-02-03'
AND fim >= '2014-02-03'

One of the problems is that if my record has the start column with value 2014-02-01 13:00:00 and I consult for WHERE inicio <= '2014-02-01', that is, same start date, is not found. Knowing that I have the operator <=.

Answer 1

Use the operator BETWEEN to facilitate and convert DATETIME for DATE.

Example:

SELECT titulo
FROM eventos
WHERE '2014-02-01' BETWEEN date(inicio) AND date(fim)

See the example on sqlfiddle.

The function date() Mysql extracts only part of date to ignore time in comparison.

---

If you want to do it without the BETWEEN:

SELECT titulo
FROM   eventos
WHERE  '2014-02-01' >= date(inicio)
  AND  '2014-02-01' <= date(fim)

See the sqlfiddle.

Answer 2

If you are to compare the start and end of the interval with a single date then an alternative to the query is to use the BETWEEN, example:

SELECT titulo
FROM eventos
WHERE '2014-02-03' BETWEEN DATE(inicio) AND DATE(fim)

Answer 3

You’re having the same problem as mine, the BETWEEN in some banks does not bring the exact value of the same day, try and >= e <= in the bank is the same thing BETWEEN, puts an extra day on your search for ex:

SELECT titulo
FROM eventos
WHERE inicio >= '2014-02-03'
AND fim <= '2014-02-04'

Answer 4

You were wrong in the logic I imagine, the right one would be the opposite of the operators:

SELECT titulo
FROM eventos
WHERE inicio >= '2014-02-03'
AND fim <= '2014-02-03'