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 < &#39;2018#09#11&#39;;
与
select a from t1 where a < &#39;2018-09-11&#39;;
与
select a from t1 where a < &#39;20180911&#39;;
所代表的意义是相同的，都是查询日期小于2018年9月11日的数据
也就是说上图的查询语句，完全可以重写为，这么做的好处？就是会使用索引，是查询更快
select a from t1 where a < &#39;2018-09-11&#39;;

When comparing a date type field with a string type date by <, >, >=, <=, between, Mysql will convert the string type date into an integer Type numbers are compared to speed up queries.

Except for the following three situations:

1, comparison of two table fields;

2, comparison of date type fields and expressions;

3 , use expressions to compare date type fields;

Reason: For the above three situations, mysql will convert the date into a string for comparison.

The following examples can all run normally:

INSERT INTO t1 (testdate) VALUES (20180912);
INSERT INTO t1 (testdate) VALUES ('20180912');
INSERT INTO t1 (testdate) VALUES ('18-09-12');
INSERT INTO t1 (testdate) VALUES ('2018.09.12');
INSERT INTO t1 (testdate) VALUES ('2018 09 12');
INSERT INTO t1 (testdate) VALUES ('0000-00-00');

SELECT testdate FROM t1 WHERE testdate >= '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) < unix_timestamp(NOW()) 
		and unix_timestamp(end_time) > 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!