Mysql datetime comparison

Question

For example, the following query works fine:

SELECT * FROM quotes WHERE expires_at <= '2010-10-15 10:00:00'; 

But this is obviously a string comparison - I was wondering if there is a function built into MySQL that specifically performs datetime comparisons.

Answer 1

... this obviously does a string comparison

No - if the date / time format matches the supported format, MySQL performs an implicit conversion to convert the value to DATETIME based on the column with which it is being compared. The same thing happens with:

 WHERE int_column = '1' 

... where the string value "1" is converted to INTEGER, since the int_column data int_column is INT, not CHAR / VARCHAR / TEXT.

If you want to explicitly convert the string to DATETIME, the best choice would be the STR_TO_DATE function :

 WHERE expires_at <= STR_TO_DATE('2010-10-15 10:00:00', '%Y-%m-%d %H:%i:%s') 

Answer 2

But it obviously does a string comparison

No. The string will be automatically added to the DATETIME value.

See 11.2. Type conversion in the evaluation of expressions.

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some transformations occur implicitly. For example, MySQL automatically converts numbers to strings as needed and vice versa.

Answer 3

I know its pretty old, but I just run into a problem, and there is what I saw in the SQL document:

[For best results when using BETWEEN with dates or time values] use CAST () to explicitly convert the values ​​to the desired data type. Examples. If you are comparing DATETIME with two DATE values, convert the DATE values ​​to DATETIME values. If you use a string constant such as "2001-1-1" compared to DATE, enter the string in DATE.

I assume it is better to use STR_TO_DATE, since they took the time to make a function just for that, and also the fact that I found this in the BETWEEN document ...