How to query date range in mysql

In mysql, you can use the "BETWEEN AND" keyword to query the specified date range. This keyword is used to determine whether the value of the field is within the specified range. The syntax is "SELECT * FROM table name WHERE field name between 'time1' and 'time2'".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How does mysql query the date range

MySQL provides the BETWEEN AND keyword to determine whether the value of the field is within the specified range.

BETWEEN AND requires two parameters, the starting value and the ending value of the range. If the field value is within the specified range, these records are returned. If it is not within the specified range, it will not be returned.

The basic syntax format for using BETWEEN AND is as follows:

[NOT] BETWEEN 取值1 AND 取值2

Among them:

• NOT: optional parameter, indicating a value outside the specified range. If the field value does not meet the specified range of values, these records are returned.

• Value 1: Indicates the starting value of the range.

• Value 2: Indicates the end value of the range.

The BETWEEN AND and NOT BETWEEN AND keywords are useful when querying records within a specified range. For example, query the age group and date of birth of students, the salary level of employees, etc.

The example is as follows:

The following table is used to query, create_time is datetime type, and query data within two date ranges.

Method 1, between...and (recommended)

SELECT * FROM k_student WHERE create_time  between '2019-07-25 00:00:33' and '2019-07-25 00:54:33'

Method 2, less than or equal to

SELECT * FROM k_student WHERE create_time >= &#39;2019-07-25 00:00:33&#39; AND create_time <= &#39;2019-07-25 00:54:32&#39;

Method 3, conversion For UNIX_TIMESTAMP comparison, if create_time is indexed, the index will not be used

SELECT * FROM k_student WHERE  UNIX_TIMESTAMP(create_time)  between UNIX_TIMESTAMP('2019-07-25 00:00:33') and UNIX_TIMESTAMP('2019-07-25 00:54:33')

Recommended learning: mysql video tutorial

The above is the detailed content of How to query date range in mysql. For more information, please follow other related articles on the PHP Chinese website!