Knowing if the date is between two columns of MySQL - Bank?

Question

Knowing if the date is between two columns of MySQL - Bank?

Navigation

#1 by (3 votes)
#2 by (1 votes)

3

I made the code below and it works, the check is done if 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)

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

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

Code Up Returns 0 Records Found

But look at the structure of the database:

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'

mysql

asked by anonymous 18.04.2015 / 14:58

2 answers

How to check enumerator value with [Flags]? How do I filter Google Maps results for a specific region?

score 3 · Answer 1

You are using the Date () function that returns the date portion of a DateTime field.

The first query works because you are comparing '2015-04-18' to the result of Date(inico) and Date(fim) that returns that format ie:

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

In the second query the comparison is done 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

score 1 · Answer 2

I researched and managed to solve the above question 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