Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

datetime

mysql

The difference between to datetimes is the number of seconds between them. This seems to work only if the datetimes occur in 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 I should be using timediff, but I'm just curious why I'm seeing this or if it's documented somewhere.

like image 603
edmz Avatar asked Jun 06 '11 16:06

edmz


People also ask

How do I subtract one date from another in MySQL?

DATE_SUB() function in MySQL is used to subtract a specified time or date interval to a specified date and then returns the date. Parameter: This function accepts two parameters which are illustrated below : date – Specified date to be modified. value addunit – Here the value is date or time interval to subtract.

What is the datatype of the value obtained when you subtract a date value from another date value in MySQL?

The DATE_SUB() function subtracts a time/date interval from a date and then returns the date.

How do I subtract hours in MySQL?

MySQL SUBTIME() Function The SUBTIME() function subtracts time from a time/datetime expression and then returns the new time/datetime.


1 Answers

MySQL is just converting strings into numbers as best it can, so that it can do the mathematical operation on them. In this case, its just stripping out all of the non numerical colons, dashes and spaces.

Try this:

SELECT (20110413155959 - 20110413160000) AS dates;

Your dates, without all the stuff that stops them being numbers - the result is -4041

like image 139
Codecraft Avatar answered Nov 07 '22 23:11

Codecraft