How to check events between 2 dates in MySQL?
Knowing that I have a table called eventos, and this table has the columns id, titulo, inicio and fim. The columns inicio and fim are of type timestamp, where the start and end date and time of a certain event is recorded. When making a select in this table, how do I catch the events of a specific date that is between the date of column start and column end ?
Example: assuming I have a record in this table with column start having value 2014-02-01 13:00:00 and column end with value 2014-02-05 22:30:00. It is an interval of 4 days, and their respective hours. How could I get 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 is with the start column with value 2014-02-01 13:00:00 and I query by WHERE inicio <= '2014-02-01', i.e. same start date, it is not found. Knowing that I have the operator <=.
4 answers
Use the BETWEEN operator to facilitate and convert DATETIME to DATE.
Example:
SELECT titulo
FROM eventos
WHERE '2014-02-01' BETWEEN date(inicio) AND date(fim)
See the example in sqlfiddle.
The function date() MySQL extracts only the part of the date to ignore the time in the comparison.
If you want to do this without BETWEEN:
SELECT titulo
FROM eventos
WHERE '2014-02-01' >= date(inicio)
AND '2014-02-01' <= date(fim)
See the sqlfiddle .
If you are comparing the start and end of the range with a single date then an alternative to the query is to use BETWEEN, Example:
SELECT titulo
FROM eventos
WHERE '2014-02-03' BETWEEN DATE(inicio) AND DATE(fim)
You are having the same problem as mine, BETWEEN in some banks does not bring the exact value of the same day, try and >= e <= in the bank is the same thing as BETWEEN, puts an extra day in your search for ex:
SELECT titulo
FROM eventos
WHERE inicio >= '2014-02-03'
AND fim <= '2014-02-04'
You got the logic wrong I imagine, the right would be the opposite of the operators:
SELECT titulo
FROM eventos
WHERE inicio >= '2014-02-03'
AND fim <= '2014-02-03'