-1

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?

Anshul Gupta
  • 265
  • 2
  • 12
  • 3
    What is March 5th 2021 divided by January 9th 2015 ? – Stu Aug 20 '21 at 15:33
  • Dates and times have a much more complex scale than scalar numbers. How do you treat leap seconds or leap years? What about time offsets? Time zones are much more complicated than you may think. – The Impaler Aug 20 '21 at 15:44
  • Both comments are kind of a diversion, but I think this is an XY problem. Press the edit button and explain your actual goal/problem. the solution is likely possible but probably won't involve those operators. – Evert Aug 20 '21 at 15:58
  • @Evert The question is straightforward. – Anshul Gupta Aug 20 '21 at 16:04
  • Ok then you have your answer. It's no. I'm curious why you chose to ask the question here, as trying it out would take you a lot less time. – Evert Aug 20 '21 at 16:05
  • @Evert As I mentioned in the question, I am learning mysql. Moreover, I was not sure how to use it. – Anshul Gupta Aug 20 '21 at 16:15
  • Again, if you have a more specific goal to try to rephrase your question to explain what you are trying to do. But if 'no' is a good enough answer, I would suggest just closing the question before it gets more downvotes. – Evert Aug 20 '21 at 16:21

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786