Boundary and range description of between in mysql

mysql between boundary range

The range of between is inclusive of the boundary values on both sides

Eg: id between 3 and 7 is equivalent to id >=3 and id<=7

The range of not between does not include the boundary value

Eg: id not between 3 and 7 is equivalent to id < 3 or id>7

SELECT * FROM `test` where id BETWEEN 3 and 7;
Equivalent to SELECT * FROM `test` where id>=3 and id<=7;
-----------------------------------------------------------
SELECT * FROM `test` where id NOT BETWEEN 3 and 7;
Equivalent to SELECT * FROM `test` where id<3 or id>7;

Note the issue of mysql between date boundaries

Boundary issues:

mysql, between start date AND end date includes the start date and excludes the end date

For example:

BETWEEN '2018-01-22' AND '2018-01-30'

The start date is 2018-01-22 00:00:00.0 and ends at 2018-01-29 23:59:59.59

CREATE_DATE in the table is varchar(21) DEFAULT NULL COMMENT 'time',

The value stored in CREATE_DATE is: year-month-day hour:minute:second:0 For example: 2018-01-29 23:45:35.0

SELECT * FROM Test a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-30'   
ORDER BY a.CREATE_DATE desc

SELECT * FROM TABEL a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-30'   
ORDER BY a.CREATE_DATE desc
    2018-01-29 23:45:35.0 20180129
    2018-01-29 23:45:33.0 20180129
    2018-01-29 00:10:58.0 20180129
    2018-01-29 00:10:45.0 20180129
    2018-01-28 23:42:23.0 20180128
    2018-01-28 23:39:39.0 20180128
SELECT * FROM TABEL a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-29'   
ORDER BY a.CREATE_DATE desc
    2018-01-28 23:42:23.0 20180128
    2018-01-28 23:39:39.0 20180128
    2018-01-28 00:13:22.0 20180128
    2018-01-28 00:13:19.0 20180128
    2018-01-27 23:23:02.0 20180127
    2018-01-22 00:09:59.0 20180122
    2018-01-22 00:09:56.0 20180122
    2018-01-22 00:01:53.0 20180122

Other problems encountered:

Another table test2 has a field for saving time: `REPORTTIME` varchar(45) DEFAULT NULL,

The value stored in this field is:

Example 1:

select * from bips_hpd_helpdesk a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d') 
BETWEEN '2018-01-16' AND '2018-01-27' ORDER BY from_unixtime(a.REPORTTIME,'%Y-%m-%d') DESC ;

Result 1:

From the results, we can see that the data for the 27th was obtained. It may be that the processing time does not include hours, minutes, and seconds.

Example 2:

select * from bips_hpd_helpdesk a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d') 
BETWEEN str_to_date('2018-01-16','%Y-%m-%d') AND str_to_date('2018-01-27','%Y-%m-%d')

Result 2:

Found the problem: When converting millisecond values to time, I found that the millisecond values saved here do not save the hours, minutes, and seconds:

from_unixtime(a.REPORTTIME,'%Y-%m-%d') AS reportTime,a.REPORTTIME,  
             str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s') AS reportTime22
        FROM test a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s') 
        BETWEEN str_to_date('2018-01-16','%Y-%m-%d %h:%i:%s') AND str_to_date('2018-01-27 %h:%i:%s','%Y-%m-%d')
     #subdate(curdate(),date_format(curdate(),'%w')-1) AND subdate(curdate(),date_format(curdate(),'%w')-8)
        ORDER BY from_unixtime(a.REPORTTIME,'%Y-%m-%d') DESC;

Viewed time value: