Know if the date is between two columns of Mysql - Database?

Question

I made the code below and it works, the check indicates whether the date is contained between the two columns and returns the result.:

SELECT * FROM  `ips_bloqueados` 
WHERE ip = '::1' AND '2015-04-18' BETWEEN date(inicio) AND date(fim)

However I would like to implement for Date and Time, I want to return if the date and time is contained between the two columns, but I’m not getting, the code does not return any results, but was to return:

SELECT * FROM  `ips_bloqueados` 
WHERE ip = '::1' AND '2015-04-18 14:10:00' BETWEEN date(inicio) AND date(fim)

Code Above Returns 0 Found Records.

But look at the database structure:

id_bloqueio   ip             inicio                   fim 

    20      '::1'    '2015-04-19 14:02:42'    '2015-04-19 14:17:42'

    19      '::1'    '2015-04-18 14:02:27'    '2015-04-18 14:17:27'

    18      '::1'    '2015-04-18 14:02:19'    '2015-04-18 14:17:19'

    17      '::1'    '2015-04-18 14:01:47'    '2015-04-18 14:16:47'

    16      '::1'    '2015-04-18 14:01:46'    '2015-04-18 14:16:46'

Answer 1

You are using the function Date() which returns the part referring to the date of a Datetime field.

The first query works because it’s comparing '2015-04-18' with the result of Date(inico) and Date(fim) that returns this format ie:

Date('2015-04-18 14:17:27') returns '2015-04-18'.

In the second query the comparison is made with '2015-04-18 14:10:00' so nothing will be found.

Note that you are comparing strings and not Datetime s.

Change the query to:

SELECT * FROM  `ips_bloqueados` 
WHERE ip = '::1' AND '2015-04-18 14:10:00' BETWEEN inicio AND fim

Answer 2

I researched and managed to resolve the issue above in two ways:

First Way:

SELECT * FROM `ips_bloqueados` WHERE ip = '::1' AND '2015-04-18 14:10:00' >= inicio and '2015-04-18 14:10:00' <= fim

Second Way - In my Most Functional Opinion:

SELECT * FROM ips_bloqueados WHERE ip = '::1' AND  '2015-04-18 14:10:00' BETWEEN inicio AND fim