How to check events between 2 dates in MySQL?

Question

How to check events between 2 dates in MySQL?

Navigation

#1 by (15 votes)
#2 by (2 votes)
#3 by (0 votes)
#4 by (-5 votes)

19

Knowing that I have a table called eventos , and this table has the columns id , titulo , inicio and fim . The inicio and fim columns are of type timestamp , where the start and end date and time of a certain event is written. When I make a% of of this table, how do I get the events of a specific date that is between the date of the start column and the end column?

Example: Assuming I have a record in this table with the start column having the value select and the end column with the value 2014-02-01 13:00: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 registry is with the start column with value 2014-02-05 22:30:00 and I query for 2014-02-01 13:00:00 , that is, the same start date, it is not found. Knowing that I have the WHERE inicio <= '2014-02-01' operator.

mysql sql timestamp

asked by anonymous 04.02.2014 / 20:30

4 answers

2

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

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

04.02.2014 / 20:40

0

You are having the same problem as mine, BETWEEN in some banks does not bring the exact same day value, try and >= e <= in the bank is the same as BETWEEN , put one more day in your search for eg:

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

20.08.2015 / 13:41

-5

You missed the logic I imagine, the opposite would be the opposite of the operators:

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

04.02.2014 / 20:33

Galton Board - Central boundary theorem How to call an external function, without sending the 'self'?

score 15 · Accepted Answer

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 .

Mysql% date() function 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 .