Can we use arithmetic operators (+, -. *, or /) on date functions in mysql?



I am learning mysql and I am stuck in the following question.

Can we use arithmetic operators (+, -. *, or /) on date functions in mysql?

Can someone help me out with it?

Answer

If you are asking whether it is possible syntactically, the answer is “yes”. But such operations do not do what you expect. Let me focus on dates for the explanation.

MySQL converts dates to numbers and does the calculation as a number. So, to answer Stu’s question in the comment:

select date('2021-03-05') / date('2015-01-09')
--> 1.003

This is calculated as 20210305 / 20150109. It would be clearer using subtraction:

select date('2021-03-05') - date('2015-01-09')
--> 60,196

Whereas the actual difference in days is 2,247.

Is this ever useful? In my opinion, no, no, no. One expects that dates will have operations as dates, so

    select date('2021-03-05') - date('2021-02-28')

would be 6 rather than 77. Disallowing such calculations would probably make it simpler for more people to write correct code.

Datetimes are treated the same way, except the values are much bigger because hours, minutes, and seconds are added to the integer.



Source: stackoverflow