Can mysql compare time?

Comparing time can be implemented in mysql, with statements such as [ SELECT testdate FROM t1 WHERE testdate >= '2018-09-12';SELECT testdate FROM t1 WHERE testdate >= 20180912;].

Recently I have found that I have often used a wrong method to compare dates. For example, in the following statement, the a field is a column of DATETIME type:

select a from t1 where DATEDIFF(a, &#39;2018-09-11&#39;) < 0;

This statement is to find all records before the date of 2018-09-11, but there is a problem with this statement. If the a field is indexed, using this type for date comparison will cause the query to not use the index. This reduces query performance.

Recommended courses: MySQL Tutorial.

I read Mysql’s documentation on date field comparison. When performing date comparison, mysql will automatically convert the date into a number for comparison. After the where condition, when using string format dates to compare with DATE, DATETIME, TIMESTAMP, and TIME type fields, the string format requirements are not strict. You can use any format delimiter to represent the date, such as "2018-09- 11", "2018#09#11", "2018&09&11" are all the same dates for mysql. If there is no delimiter, such as "20180911", it is the same as "2018-09-11" or other dates with delimiters. For example, in the figure below

select a from t1 where a 9898dff2efce06951d62807a449844c3, >=, e7c8434fbbbf00f6fd91b67da0d0fa96= '2018-09-12';
SELECT testdate FROM t1 WHERE testdate >= 20180912;
SELECT MOD(testdate,100) FROM t1 WHERE testdate >= 20180912;
SELECT testdate FROM t1 WHERE testdate >= '20180912';

Mysql allows storing "0000-00-00" as the "0" value of the DATE type, also called a virtual date. In some scenarios, it is more convenient than storing NULL values. If an illegal date value is saved into a DATE type field, MySQL stores it as "0000-00-00" by default. If storing "0" values ​​is not allowed, enable the NO_ZERO_DATE parameter.

You can also use the unix_timestamp function to convert character time into a unix timestamp.

select meeting_id,meeting_name,start_time,end_time from meeting_data 
	where meeting_id REGEXP '[0-9]{4}0001' 
		and unix_timestamp(start_time) 98af3aed4642ba62db3d3875db9cdaa5 unix_timestamp(NOW());

The above is the detailed content of Can mysql compare time?. For more information, please follow other related articles on the PHP Chinese website!