What is the behavior for a minus operator between two dates in MySQL?

Question

The difference between dates is the number of seconds between them. This seems to work only if the lifetime occurs at the same hour.

Why is this?

mysql> update events set created_at = "2011-04-13 15:59:59", fulfilled_at ="2011-04-13 16:00:00" where id = 1; mysql> select fulfilled_at - created_at, timediff(fulfilled_at, created_at) from events where id = 1; +---------------------------+------------------------------------+ | fulfilled_at - created_at | timediff(fulfilled_at, created_at) | +---------------------------+------------------------------------+ | 4041.000000 | 00:00:01 | +---------------------------+------------------------------------+ 

I know that I should use timediff, but I'm just wondering why I see it or documented it somewhere somewhere.

Answer 1

MySQL simply converts strings to numbers as much as possible so that it can perform a mathematical operation on them. In this case, it is simply removed by all the odd colons, dashes, and spaces.

Try the following:

 SELECT (20110413155959 - 20110413160000) AS dates; 

Your dates, without any material that puts them in numeric numbers - the result is -4041

Answer 2

Recall that mysql has two different types depending on date and time: The suffix _SUB is designed to subtract the date minus the interval that returns the date strong>. The suffix _DIFF is designed to get the difference between two dates by returning the interval (BTW, note that only the first has the opposite analogue: _ADD )

The +/- signs must be used for the first (ADD / SUB), so MYSQL expects an interval as the second argument.

 DATE = DATE_ADD(DATE,INTERVAL) Also accepts + DATE = DATE_SUB(DATE,INTERVAL) Also accepts - INTERVAL = DATE_DIFF(DATE,DATE ) 

See the doc here , the bit starts with:

 Date arithmetic also can be performed using INTERVAL together with the + or - operator... 

Therefore, it is incorrect to use the - value to accept the difference between the two dates. Now MYSQL, faced with incorrect conclusions, is trying to do everything possible (instead of throwing an error), sometimes it's good, sometimes not.