STR_TO_DATE() vs CONSTANT when comparing DATETIME field

Question

There is a table with DATETIME field named 'created_at'. I try execute two queries like this:

1. SELECT * FROM myTable WHERE created_at BETWEEN '2015-03-15 10:25:00' AND '2015-03-25 10:30:00';
2. SELECT * FROM myTable WHERE created_at BETWEEN STR_TO_DATE('2015-03-15 10:25:00', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2015-03-25 10:30:00', '%Y-%m-%d %H:%i:%s');

I always used the first query, but recently came across an article in which describes that the second approach is the best way to compare DATETIME. Unfortunately, it does not contain any explain why that approach is the best way.

Now, I have some questions:

• Is there any difference between these two approaches?
• Which way is more preferable?

Thanks!

Answer 1

I much prefer to put the constants in directly. I believe that MySQL will process the str_to_date() function only once for the query, because the arguments are constants. However, I don't like to depend on this optimization.

The advantage to str_to_date() is that it should be independent of internationalization settings so the result should be unambiguous. However, the use of ISO standard formats should be equivalent, and that is the structure of your constants.

However, that aside, a better way to write the query is:

SELECT *
FROM myTabl
WHERE created_at >= '2015-03-15 10:25:00' AND
      created_at < '2015-03-25 10:30:00'

I am guessing that you don't really want 10 days, five minutes and one second in the interval, but want exactly 10 days and five minutes. In any case, the use of between with dates and datetimes can cause unexpected results, particularly when you do:

where datetime between '2015-03-15' and '2015-03-16'

If you think you are getting two dates, you are wrong. You are getting all date times on the first day plus midnight on the second.