The doubt came to me answering a question here on Stackoverflow.
It was about getting only the date (without the time) of a column DATETIME
, to compare it using BETWEEN
.
Obtaining that date can be done:
- Using
DATE
:SELECT DATE(col_fecha) fecha;
- Using
DATE_FORMAT
:SELECT DATE_FORMAT(col_fecha,'%Y-%m-%d') fecha;
Are there differences in the use of one or another function, for example in terms of performance ?
MySQL documentation doesn't say anything about it, hence my doubt.
Consulting the MySQL source code in the conversion functions from
DATETIME
toDATE
and vice versa , it can be seen that converting a fieldDATETIME
to a fieldDATE
is simple, just change a property of the classMYSQL_TIME
and the work is practically done:Example conversion from
TIME
toDATETIME
:Example of mixing data
DATETIME
andTIME
:This last step is a bit more complex because if there are negative hours or hours outside the 24-hour range, the date is recalculated.
Link to the date and time format parsing function (
parse_date_time_format
) .Empirical evidence:
Empirical tests can be done to check that the hypothesis is correct ( see online proof ):
Results:
It can be seen that there is hardly any appreciable difference between obtaining the normal data and those treated with the function
DATE()
(even in the sample of 5 results, the use ofDATE
), however, there is a more notable difference when to use the functionDATE_FORMAT()
.In summary: after the overhead of parsing the query structure, resolution flow, etc., finally a date data conversion is much faster than any call to a function that has as an additional overhead the calculation of the text format that must return.
I assume that
Date()
:SELECT DATE(col_fecha) fecha;
is faster than usingDATE_FORMAT
, ie.When you use
Date()
, you are not passing it arguments, the format is defined, instead,Date_format()
it has to evaluate the format of the date to each record, for what you want or not, it will be slower, and it is something that would be noticed in large data scales.The advantage it has
Date_format()
overDate()
is that you can define the format yourself,Date
as such it does not allow you to select it.I clarify that this is my own deduction , I could be wrong.